Error in VBA Find Function

[wsnyder]

Hi all,

Using Excel 365.

My Function is returning an error:

Public Function FindColumnHeader(rng As Range, _
                                 SearchTerm As String) As Long

    FindColumnHeader = rng.Find(what:=SearchTerm, _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                MatchCase:=False).Column

End Function

The range exists and is correct
The SearchTerm exists.
What am I missing?

Here's the Function that is calling the FindColumnHeader Function

Private Function GetColumnArray(v As Variant, _
                                rng As Range) As Variant

    Dim i As Long
    Dim x As Long
    Dim crit As String
    Dim tempColumnArray() As Long
    x = 1
    
    For i = LBound(v) To UBound(v)
        ReDim Preserve tempColumnArray(x)
        crit = CStr(v(i))
        tempColumnArray(x) = FindColumnHeader(rng:=rng, _
                                              SearchTerm:=crit)
        x = x + 1
    Next i
    
    GetColumnArray = CVar(tempColumnArray)

End Function

Thanks,
-w

 

[Alex Blakenburg]

Do you want to run your function with these 2 additional debug.print lines ?

• Check the range is the range containing your find value
• There should be no gaps between your Search Term and the "~" I have added to the start and end.
• Last value No Spaces should be TRUE.

Public Function FindColumnHeader(rng As Range, _
                                 SearchTerm As String) As Long
    Debug.Print rng.Address
    Debug.Print "Search Term=~"; SearchTerm & "~"; vbTab; "Length="; Len(SearchTerm); vbTab; "No Spaces="; SearchTerm = Trim(SearchTerm)
    
    FindColumnHeader = rng.Find(what:=SearchTerm, _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                MatchCase:=False).Column

End Function

 

[wsnyder]

Thanks Alex,

Here is the ouput:

$1:$1
Search Term=~Prj Def~ Length= 7 No Spaces=True

Prj Def is currently in $D$1. It is 7 char's long

The problem was I was using Set wb = ThisWorkbook
I should have used a different workbook Set wb = Workbooks("myOtherWorkbook.xlsx")

I updated and that bit works as expected.

Thanks,
w

 

[Alex Blakenburg]

I appreciate you taking the time to provide details of the testing. I gather it was not the solution but I hope it at least helped.
I think it would be appropriate for you to change the Mark the Solution over to your own Post (#3) since that is really what solved your issue (and others may find helpful).

Have a good day.