Karl_mark
asked on
Constructing a Range to use in Find Method Excel VBA
I have created a generic function in VBA which uses the find method to search a range:
I then want to call this function from controls on user forms. Easy enough so far... However, I always get a type mismatch error when it tries to execute the function. I've tried passing the range as a range datatype as well as a variant, but it doesn't seem to make any difference.
I also tried adding the find code to the sub, but still get a type mismatch error. So, I'm using it incorrectly. What is wrong with the code?
Public Function fCheckValueExists(ws As Worksheet, rng As Variant, _
strValue As String) As Variant
fCheckValueExists = ws.Range(rng).Find(strValue, ActiveCell, , , , xlNext, False, , False).Activate
End Function
I then want to call this function from controls on user forms. Easy enough so far... However, I always get a type mismatch error when it tries to execute the function. I've tried passing the range as a range datatype as well as a variant, but it doesn't seem to make any difference.
I also tried adding the find code to the sub, but still get a type mismatch error. So, I'm using it incorrectly. What is wrong with the code?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect Sid. Works a treat.
Good point by Rorya as well!
Good point by Rorya as well!
Open in new window
Sid