Solved

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

Posted on 2013-11-19
6
349 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 47

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 47

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
Independent Software Vendors: 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!

 
LVL 47

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 47

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

734 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