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

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
LVL 1
gabrielPennybackReliability Business Tools Analyst IIAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Martin LissConnect With a Mentor Older than dirtCommented:
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
 
Martin LissOlder than dirtCommented:
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
 
gabrielPennybackReliability Business Tools Analyst IIAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
Martin LissConnect With a Mentor Older than dirtCommented:
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
 
gabrielPennybackReliability Business Tools Analyst IIAuthor Commented:
Works great, Martin, thanks!
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.