VBA Type Mismatch when Using .Find

[AlexB123]

Hi all,

I'm trying to return a row number of a specific string value, based off of the results of a range.find formula. I don't understand why, but the function is returning an integer and not a long data type. I have triple checked my variables, and "filelastrow" as well as "UCRMainLastRow" are both of type long. I understand that .Row is supposed to return a long also.

I attempted to wrap the function with CLng(), but I am still getting an int and type mismatch.

Any help is appreciated!

filelastrow = macroSh5.Range("A2:A" & UCRMainLastRow).Find(What:=strPlanNumberWholeCell, After:=ActiveCell, LookIn:=xlValues, LookAt _
                    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
                    False, SearchFormat:=False).Row

 

[dmt32]

Hi,

try changing part of your code shown in RED

filelastrow = macroSh5.Range("A2:A" & UCRMainLastRow).Find(What:=strPlanNumberWholeCell, After:=ActiveCell, LookIn:=xlValues, LookAt _
                    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
                    False, SearchFormat:=False).Row

to this

filelastrow = macroSh5.Range("A2:A" & UCRMainLastRow).Find(What:=strPlanNumberWholeCell, After:=Cells(2, 1), LookIn:=xlValues, LookAt _
                    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
                    False, SearchFormat:=False).Row

and see if this resolves the error.

Dave

 

[AlexB123]

Many thanks!

Racked my brain for awhile on that one...