Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

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
0
gabrielPennyback
Asked:
gabrielPennyback
  • 4
  • 2
2 Solutions
 
Martin LissRetired ProgrammerCommented:
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
 
gabrielPennybackAuthor 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
 
Martin LissRetired ProgrammerCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Martin LissRetired ProgrammerCommented:
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
 
gabrielPennybackAuthor Commented:
Works great, Martin, thanks!
0
 
Martin LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

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.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now