Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA 'Find What' function that keeps looking for a second match in the previous column

Posted on 2013-11-19
6
Medium Priority
?
353 Views
Last Modified: 2013-11-20
How do I modify or add to this line so that it finds not just the first instance of D4's value in [K8:K788], but keeps going to find the first instance of [D4] for which the cell in Column J contains the value in [D5], e.g. "41"?

[K8:K788].Find(What:=[D4], LookIn:=xlValues)

Open in new window

Thanks,
John
0
Comment
Question by:gabrielPennyback
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39661325
I'm not sure I understand the desired result. If a match occurs in both columns K and J what should the find return? And is the range of rows in J the same as in K? And 3rd question, is there any data below row 788?
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 39663461
Hi Martin, thanks for responding. OK, let's say the value in [D4] is "AFR."
Then this search:
[K8:K788].Find(What:=[D4], LookIn:=xlValues) 

Open in new window

might bring us to [K122], the first occurrence of "AFR". The problem is that [J122] = 39, and what I need is the first occurrence of "AFR" in Column K for which the J column value is 41 (the value in D5). And that might be [K184].

Does that make sense yet?

And no, there's nothing beyond row 788.

Thanks,
John
0
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 39663621
Here's a macro you can use.

Sub MatchBoth()
Dim lngRow As Long

With ActiveSheet
    For lngRow = 1 To .UsedRange.Rows.Count
        If .Cells(lngRow, 10).Value = .[D5] And _
           .Cells(lngRow, 11).Value = .[D4] Then
            MsgBox "Match found in row " & lngRow
            Exit For
        End If
    Next
End With

End Sub

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 2000 total points
ID: 39663628
Or as a function.
Function MatchBoth() As Long
Dim lngRow As Long

With ActiveSheet
    For lngRow = 1 To .UsedRange.Rows.Count
        If .Cells(lngRow, 10).Value = .[D5] And _
           .Cells(lngRow, 11).Value = .[D4] Then
            MatchBoth = lngRow
            Exit For
        End If
    Next
End With

Open in new window

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 39663968
Works great, Martin, thanks!
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39664028
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question