>.Find in two columns

[AlexanderBB]

Set found = .Range(rCol).Find(What:=Wanted, LookIn:=xlValues, LookAt:=xlPart)

Wanted exists in 2 parts in Column S and Column T
But I'm having no luck setting a range to both columns, (which I read is possible).
How's it done ? Also, can it be done so both xlPart and xlWhole could be applied. Thanks.

 

[Joe4]

Set found = .Range(rCol).Find(What:=Wanted, LookIn:=xlValues, LookAt:=xlPart)

Wanted exists in 2 parts in Column S and Column T
But I'm having no luck setting a range to both columns, (which I read is possible).
How's it done ? Also, can it be done so both xlPart and xlWhole could be applied. Thanks.

Please show us some sample data, and what exactly you want trying to do.
What does your data look like?
How/where are all your variables being set (show us that part of the code too)?

 

[AlexanderBB]

"John" is in Col S, "Smith" is in Col T
Find What is "John Smith"
What's the best way to get the address? Can the .Find method be used?

 

[Joe4]

Here is one way:

Sub MyMatch()

    Dim fName As String
    Dim lName As String
    Dim lr As Long
    Dim r As Long
        
'   Set first and last name to find
    fName = "John"
    lName = "Smith"
    
'   Find last row in column S with data
    lr = Cells(Rows.Count, "S").End(xlUp).Row
    
'   Loop through all rows
    For r = 1 To lr
'       See if column S matches first name
        If Cells(r, "S").Value = fName Then
'           See if column T matches last name
            If Cells(r, "T").Value = lName Then
'               Return row number and quit sub
                MsgBox "Match found in row " & r, vbOKOnly, "SUCCESS!"
                Exit Sub
            End If
        End If
    Next r
    
'   Message if no matches found
    MsgBox "No match found!", vbOKOnly, "FAILURE!"
    
End Sub

 

[AlexanderBB]

Thanks Joe, so no way to avoid a loop or look at both columns together as a range. Somehow. Rightoo...

 

[Joe4]

Thanks Joe, so no way to avoid a loop or look at both columns together as a range. Somehow. Rightoo...

I am not saying there is not a way - there may be, but it is beyond my level of comprehension.
The original "Find" command you tried using cannot search two different columns for two different values at the same time.
I supposed you could that command in a single Loop to search for the first value in the first column, and then check the second column, and if not found, continue on in the loop.
It would probably be a little faster, but you would need to add extra coding in to avoid continuously looping.

How is the performance of the code I gave you?
How long does it take to run?

 

[Alex Blakenburg]

The original "Find" command you tried using cannot search two different columns for two different values at the same time.
I supposed you could that command in a single Loop to search for the first value in the first column, and then check the second column, and if not found, continue on in the loop.
It would probably be a little faster, but you would need to add extra coding in to avoid continuously looping.

There seems to be a misconception out there that Find is fast. It might be faster than looping through each Cell in the Range but it is much slower than using either the worksheet Match function or a Variant Array. In Charles Williams testing which is a very close match to @AlexanderBB's use case, it is at least 7.5 times slower than using Match.
The strength of Find is if you need to search a wider range of columns or need some of the other find options ie search for format or comments etc.

MATCH vs FIND vs Variant Array VBA Performance Shootout in Excel 2010

When searching unsorted data in VBA I have always tended to use MATCH rather than FIND, mainly because early versions of Excel before Excel 2003 did not allow you to use FIND in UDFs. But prompted …

fastexcel.wordpress.com

 

[Fuji]

Sub test()
    Dim s$(1), x
    s(0) = "John": s(1) = "Smith"
    With ActiveSheet
        With Intersect(.UsedRange, .Columns("s:t"))
            '↓ case insensitive
            x = Filter(.Parent.Evaluate("transpose(if((" & .Columns(1).Address & "=""" & _
            s(0) & """)*(" & .Columns(2).Address & "=""" & s(1) & """),row(" & .Address & ")))"), False, 0)
            '↓ case sensitive
            'x = Filter(.Parent.Evaluate("transpose(if(exact(" & .Columns(1).Address & ",""" & _
            s(0) & """)*exact(" & .Columns(2).Address & ",""" & s(1) & """),row(" & .Address & ")))"), False, 0)
            If UBound(x) = -1 Then
                MsgBox "No match"
            Else
                MsgBox "Found in row(s)" & vbLf & Join(x, vbLf)
            End If
        End With
    End With
End Sub

 

[AlexanderBB]

All interesting, Thanks for the replies. The link about MATCH was very informative but a bit beyond me.

The string to find is contained in two adjacent columns. Possible rows are 2-18609. It isn't known which part is in each column. Or how many spaces (words) might be involved.
Looking for "John" in Col S and "Smith" in Col T will fail if "John Smith" isn't split as expected
I need to treat the 2 columns as if they were one.

I was using 'find to get a range for split(John Smith"," ")(0) then looking through all the results for cells(found.row,"S")= split(John Smith"," ")(1)
It seems to be ok, but I'm not 100% sure yet. What does work, and I was surprised how quick it was,

Debug.Print Now
For i = 2 To 18609
    If Cells(i, "S") & " " & Cells(i, "T") = "John Smith" Then Debug.Print i
Next
Debug.Print Now

30/12/2024 10:53:22 a.m.
1181
18569
30/12/2024 10:53:23 a.m.

Maybe 18609 rows is not a lot for Excel.

Joes results were
30/12/2024 10:51:18 a.m.
Match found in row 1181 0 SUCCESS!
30/12/2024 10:51:18 a.m.

And Fuji, which seemed the fastest
30/12/2024 10:54:49 a.m.
Found in row(s)
1181
18569
30/12/2024 10:54:49 a.m.

So there's not a lot in it. Maybe my loop is a good as any, but I suspected there'd be something better using a range. (I hadn't really used these before.) And it isn't case insensitive or whole-part able yet.

 

[Alex Blakenburg]

Its a fairly simple matter to convert the code you just posted to using an array and it will run much faster.
The slowest part of the code is normally reading the cells one by one. By reading it into an array you only perform a single read operation and the rest is done in memory. Give it a try:

Sub TestArray()

Dim i As Long
Dim arr As Variant

Debug.Print Now
arr = Range("S1:T" & Cells(Rows.Count, "S").End(xlUp).Row)

For i = 2 To UBound(arr)
    If arr(i, 1) & " " & arr(i, 2) = "John Smith" Then Debug.Print i
Next
Debug.Print Now

End Sub