"FIND" Macro

[jamieleeuk]

Hi,

I have recorded the following code by doing CTRL+F, entering a search string and pressing Find.

Sub test()
' Cells.Find(What:=***THE VALUE IN CELL W3***, After:=ActiveCell, LookIn:=xlFormulas, LookAt
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
End Sub

Please can someone let me know what I need to put in place of the above ***THE VALUE IN CELL W3*** to enable a user to enter a search string in cell W3 and for it (if a matching value found) for it to become the active cell?

thanks in advance
Jamie

 

[VoG]

Try

Sub test()
Dim Found As Range
Set Found = Cells.Find(What:=Range("W3").Value, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not Found Is Nothing Then Found.Select
End Sub

 

[jamieleeuk]

Thanks Peter,

Whereas I don't receive any errors, the active cell doesn't appear to change to the cell that is Found.
Maybe a better way for me to see if there's a match is if the matched value could somehow change colour?

IE, if value in W3 is found elsewhere on the sheet, then highlight the value in its place?

Is that possible?

 

[VoG]

See if this works

Sub test()
Dim Found As Range
Set Found = Cells.Find(What:=Range("W3").Value, LookIn:=xlValues, LookAt:=xlPart)
If Found Is Nothing Then
    MsgBox "Not found", vbExclamation
Else
    Found.Interior.ColorIndex = 6
End If
End Sub

 

[jamieleeuk]

Peter. thanks.

It's almost there and from whats on the code already, I think I'll be able to finish it off.

Thanks again
Jamie