Please help with Find Function in Excel VBA

[rogeryung]

Hi, I'm trying to do the following:

Based on user input via drop down lists, a value will show up in cell W4. I want to have a macro button set up next to that value that will search the entire worksheet for whatever value shows up in cell w4. The trouble is that all I can find examples of are macros that search based on a text string that is either embedded in the macro, or a message box prompts the user to type in the text to search for.

Basically I want to eliminate the user having to type anything in addition to selecting via the drop down lists. Does anyone know what the vba syntax should be for that?

Thanks
Roger

 

[Andrew Poulsom]

Just reference that cell's value for the What argument, eg:

With ActiveSheet
    .Cells.Find(What:=.Range("W4").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
End With

 

[rogeryung]

thanks..I didn't know what the proper syntax was for that, but was what I was looking for. Thanks again!
Roger