Solved

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

Posted on 2013-11-19
6
328 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
  • 4
  • 2
6 Comments
 
LVL 46

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 46

Accepted Solution

by:
Martin Liss earned 500 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now