Solved: Correct method for a second FindThis criteria

[LLEW1]

Hi all,

Im using the following:

CllRg = Range("Sheet1!G23").Value


as the search criteria for the following FindThis statement

FindThis = (CllRg)

This works fine, but I want to add a second find criteria, so the result would be that FindThis locates as cell based on two criteria; once its found the first cell, to then find the second criteria in the same row as the cell it just found.

How might I create a second value for the the CllRg to be found?

[Bob Phillips]

I can't see an search in there, just setting a variable to a value.

Can you expand on your problem a bit?

[mdmackillop]

Option Explicit
Sub FindTwo()
Dim Fnd1 As String, Fnd2 As String
Dim FirstAddress As String
Dim c As Range, d As Range
Fnd1 = "Test"
Fnd2 = "Trial"
With Worksheets(1).Range("a1:a500")
Set c = .Find(Fnd1)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Set d = c.EntireRow.Find(Fnd2, LookIn:=xlValues)
If Not d Is Nothing Then
'Do Stuff
c.Interior.ColorIndex = 8
d.Interior.ColorIndex = 8
End If
Set c = .Find(Fnd1, after:=c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub

[LLEW1]

Thanks xld and 'Mack'; almost there with your suggestion Mack.
xld - yes should have included a little more detail up front.

Have taken Mack's suggestion and almost works, but thought best if I just provide a simple example of the data as below, this is Sheet1. Looking to search the <Location> column to find criteria (for example 'West') then locate the second criteria (for example 'J500').

Mack's suggestion does this for me - however I didnt make clear that the data could include several instances of the same Location, as below.

I need copy and paste the row located into Sheet2. The Location column wont be the same column on each occassion but the first criteria being searched will only be in the Location column.

Thanks again.

[Bob Phillips]

Dim mpRow As Long

On Error Resume Next
mpRow = Application.Evaluate("Match(1,(A2:A20=""J300"")*(D2:D20=""West""), 0)")
On Error GoTo 0
If mpRow > 0 Then

Rows(mpRow).Copy Worksheets("Sheet5").Range("A1")
End If

[LLEW1]

Hi again xld,

trying yours advice on the true data now - thanks.
Will let you know how I go.

[LLEW1]

Thanks xld - this is close, however its picked up the row above, so from the example data for the criteria given I got Row10, as below:

F100 Fruit 572.00 North

Not Row 11; am I applying it incorrectly?

[Bob Phillips]

Sorry, my bad.

Because Match returns a row index, that is not the row number, we have to adjust it to the start row matched against. So if we matcah against A2:A20, you have to add 1, A3:A20, add 2 and so on.

[LLEW1]

Thanks xld - understand, will try, let you know --- thanks...

[LLEW1]

Hi xld, your help worked...
all good - thanks again; one remaining issue Im now using a parameter as in

Dim CllRg As String
for the second lookup. Paremeter is read fine in rest of script, but not in this line. Ive tried it without the brackets, but clearly have the syntax wrong around <CllRg> in the line, as below..


mpRow = Application.Evaluate("Match(1,(C2:C20=""F100"")* (HK2:HK20 = (CllRg)), 0)")
.any thoughts xld or others - really appreciated; and thanks again.

[mdmackillop]

Sub FindTwo()
Dim Fnd1 As String, Fnd2 As String
Dim FirstAddress As String
Dim c As Range, d As Range
Fnd1 = "West"
Fnd2 = "J300"
With Worksheets(1).Range("d1:d500")
Set c = .Find(Fnd1)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Set d = c.EntireRow.Find(Fnd2, LookIn:=xlValues)
If Not d Is Nothing Then
'Do Stuff
c.EntireRow.Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1)

End If
Set c = .Find(Fnd1, after:=c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub

[LLEW1]

Thanks Mack,

Youve been really helpful on this. Ive used a mix of your solution with others, and have fair result; now just a last (hopefully) impasse.

As mentioned above - Im using the parameter CllRg for one of the variables <Location>, but when Im incorrectly referring to it.
This works fine

mpRow = Application.Evaluate("Match(1,(C2:C20=""F100"")* (HK2:HK20 = ""West""), 0)") But when Insert the parameter CllRg, as below to replace West, its not recognised - though it is elsewhere throughout the script - Im missing the correct syntax to insert CllRg as a parameter into the Match statement.

mpRow = Application.Evaluate("Match(1,(C2:C20=""F100"")* (HK2:HK20 = (CllRg)), 0)") Do you have an idea on how I might include it? Or anyone else?
Thanks again..

[mdmackillop]

Note that this will return the first result Row 8, but not Row 11
Dim mpRow As Long
Dim Fnd1 As String, Fnd2 As String

Fnd1 = Range("G1")
Fnd2 = Range("H1")

On Error Resume Next
mpRow = Application.Evaluate("Match(1,(A1:A20=" & """" & Fnd2 & """" & ")*(D1:D20=" & """" & Fnd1 & """" & "), 0)")
On Error GoTo 0
If mpRow > 0 Then

Rows(mpRow).Copy Worksheets("Sheet2").Range("A1")
End If

[LLEW1]

Hi Mack and thanks.

will work with this next - and confirm results.

Thanks again.