How do I use FIND function in VBA with search sting as a Relative Reference?

Question

Here's an image with Excel sample data. Columns used are A, B, F & G.

I am trying to find Part from a Column F into Column A to see if it is in Column A or not.

I know that I can use FIND Function in VBA with (What:="Jane Doe") with a double quotes text. But, is it possible to use a relative reference such as ActiveCell.Offset(0,-1).Value within the FIND function in VBA?

    Dim SearchRange As Range
    Dim ECOCell As Range
          
    Range("G2").Select
        
    Set SearchRange = Range("A2", Range("A2").End(xlDown))
        
    Set ECOCell = SearchRange.Find(What:=ActiveCell.Offset(0, -1).Value, MatchCase:=True, LookAt:=xlWhole)
    
    Do While ActiveCell.Offset(0, -1).Value <> ""
        If ECOCell Is Nothing Then
            ActiveCell.Value = "No"
        Else
            ActiveCell.Value = "Yes"
        End If
        
        ActiveCell.Offset(1, 0).Select     
    Loop    
End Sub

Type `=SUM(IFERROR(SEARCH("*"&F2&"*",A$2:A$5), 0)) > 0` into G2, press [Ctrl+Shit+Enter](https://support.microsoft.com/en-us/office/create-an-array-formula-e43e12e0-afc6-4a12-bc7f-48361075954d?ui=en-us&rs=en-us&ad=us) and drag the result down. — GSerg, Jul 27 '20 at 12:47
In O365 `=IF(ISERROR(XLOOKUP("*" & TRIM(F2) & "*",$A$2:$A$5,$A$2:$A$5,,2)),"No","Yes")`. Added `TRIM` to cell reference to remove the space before _Part H_. — Darren Bartrup-Cook, Jul 27 '20 at 12:55
Note that `xlookup` requires Office 365 subscription though. — GSerg, Jul 27 '20 at 13:00
What seperates these parts in column A, simple spaces? Also, what version of Excel are you using, and are you open for non-VBA alternatives? — JvdV, Jul 27 '20 at 13:02
@JvdV Not just the spaces. Part numbers look actually like "310-00078", "TMF12345", "N000083", or anything like that. — Nikhil Holmes, Jul 27 '20 at 13:04
Can you actually update the question with that information to make it a more [mcve] — JvdV, Jul 27 '20 at 13:04
@GSerg Thanks. It works; but, I'd still ask you guys the same question. Is there a way to do with a FIND(What:=ActiveCell.Offset(0,-1).Value) as a relative reference? I mean, my list is 130,000 rows long. VBA code takes approximately 12 minutes to search all one by one. This formula takes some load as well; though still have to see how long. Thanks. — Nikhil Holmes, Jul 27 '20 at 13:06
If you are asking whether you can execute a single Find that will find separate results for each cell from the source range, then no, you can't. You can speed up your VBA code significantly by [getting rid of the `Select`s](https://stackoverflow.com/q/10714251/11683), but it's going to fundamentally perform a separate Find per source cell. — GSerg, Jul 27 '20 at 14:13

score 0 · Answer 1 · answered Jul 27 '20 at 13:17

Yes, you can reference a cell rather than a text string.

There's a few things wrong with your code though.
I take it ActiveCell will be looking at each cell in column F. I'd look in column G and put the Yes/No offset to that.
There's a few problems with using Select & Active for which I'll point you to this post.

End(xlDown) could also cause problems if there's a gap in your data. Best to start from the bottom and work up.

Your FIND doesn't step through the values in column G either. It looks like it uses the first result against all the values.
It's also looking at xlWhole so will never find Part A, B, C, F or G as they share the cell with other parts (this could cause problems in itself if you have Part A and Part ABCD).

Try this code:

Sub Test()

    Dim SearchRange As Range
    Dim PartsRange As Range
    Dim Part As Range
    Dim ECOCell As Range
    
    With Worksheets("Sheet1")
        Set SearchRange = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
        Set PartsRange = .Range("F2", .Cells(.Rows.Count, 6).End(xlUp))
        
        For Each Part In PartsRange
            Set ECOCell = SearchRange.Find(What:=Trim(Part), After:=.Range("A2"), LookAt:=xlPart, MatchCase:=False)
            
            If Not ECOCell Is Nothing Then
                Part.Offset(, 1) = "Yes"
            Else
                Part.Offset(, 1) = "No"
            End If
        Next Part
    End With

End Sub

How do I use FIND function in VBA with search sting as a Relative Reference?

1 Answers1