Link to home
Start Free TrialLog in
Avatar of Karl_mark
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:

   
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

Open in new window


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?
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Try something like this

Public Function fCheckValueExists(ws As Worksheet, rng As String, _
strValue As String) As String
    Dim aCell As Range
    
    Set aCell = ws.Range(rng).Find(strValue, ActiveCell, , , , xlNext, False, , False)
    
    If Not aCell Is Nothing Then fCheckValueExists = aCell.Address
End Function

Open in new window


Sid
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Karl_mark
Karl_mark

ASKER

Perfect Sid. Works a treat.
Good point by Rorya as well!