How to use a variable in the FIND statement

[Bovey44]

I have he following command in my program:

Sheets("PLN").Range("F2:F30000").Find(what:=hold_key).Select

I've seen where it's possible to use a literal after the what:= as for instance Find(what:="Rose").Select - however, I want to be able to use a variable - as I have in the command where hold_key is the variable.

Is this possible? I keep getting an error "object variable or with block variable not set" I have hold_key setup in a dim statement as a string

 

[Joe4]

That is the error you get when it cannot find the value you are looking for.

A better way to handle it is like this:

    Set rng = Sheets("PLN").Range("F2:F30000").Find(what:=hold_key)
    
    If rng Is Nothing Then
        MsgBox "Cannot find " & hold_key & " in that range"
    Else
        rng.Select
    End If

 

[Bovey44]

That is the error you get when it cannot find the value you are looking for.

A better way to handle it is like this:

    Set rng = Sheets("PLN").Range("F2:F30000").Find(what:=hold_key)
   
    If rng Is Nothing Then
        MsgBox "Cannot find " & hold_key & " in that range"
    Else
        rng.Select
    End If

Thank you - I appreciate the help.

 

[Joe4]

You are welcome.