• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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?
0
Karl_mark
Asked:
Karl_mark
  • 2
2 Solutions
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
OR as a boolean.

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

Open in new window


Sid
0
 
Rory ArchibaldCommented:
You need to remove the ActiveCell part from the Find call as well, unless you can guarantee that the activecell will be part of the range you specify (which looks unlikely)
0
 
Karl_markAuthor Commented:
Perfect Sid. Works a treat.
Good point by Rorya as well!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now