Take user-defined range as input to function

Question

I am relatively new to VBA in excel. I would like to write a custom function that takes a range defined by the user as input and then searches for a particular word within that range. So far, the following code works, but it only searches in the predefined range "a2:g2", but I would want something that would change to "a3:g3", etc as the user fills down:

Function findme()
    With Worksheets("Filtered").Range("a2:g2")
    Set c = .Find("XXXX", LookIn:=xlValues)
    If Not c Is Nothing Then
      findme = "Match Found"
    Else: findme = "No match"
    End If
End With

I thought this should work, but it does not. It returns "VALUE"

Function findme(myrange as range)
    With Worksheets("Filtered").Range(myrange)
    Set c = .Find("XXXX", LookIn:=xlValues)
    If Not c Is Nothing Then
      findme = "Match Found"
    Else: findme = "No match"
    End If
End With

nutsch · Accepted Answer · 2013-12-04 17:17:10Z

2

myrange doesn't get passed to the function as an address / string, it's an actual range. The way you use it in your code (Worksheets("Filtered").Range(myrange)) requires an address. You could keep your code and use myrange.address instead of just myrange, or you can use the fact that your variable is all you need to continue.

Function findme(myrange as range) as string
    With myrange
    Set c = .Find("XXXX", LookIn:=xlValues)
    If Not c Is Nothing Then
      findme = "Match Found"
    Else: findme = "No match"
    End If
End With

answered Dec 4, 2013 at 17:17

nutsch

5,9622 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3066858 Over a year ago

O yes nutsch. This is exactly the answer. Thank you.

nutsch Over a year ago

Perfect. If the answer works for you, please accept it as a solution. Welcome to SO.

Collectives™ on Stack Overflow

Take user-defined range as input to function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related