Find

[Guest]

How do i code a macro to find a value in a specific range of cells. The value
to locate will be typed by the user in a cell

Cells.Find(What:=B3, LookIn:=b5:b500).Activate ??????

Any suggestions please

thanks

 

[Dave Peterson]

Dim FoundCell as Range
with activesheet
set foundcell = .range("b5:b500").find(what:=.range("b3").value)
if foundcell is nothing then
msgbox "not found"
else
foundcell.activate
end if
end with

 

[Guest]

Hi,

Try;

Range("b5:b500").Find(What:=Range("B3")).Activate

Regards,

Chris.

 

[Guest]

Dave,

I tried this and it worked for me to an extent. It found the correct cell
but then I get
Run-time error '424':
Object required

here is what I have

Sub findreq()
Dim FoundCell As Range
With ActiveSheet
Set FoundCell = .Range("b4:b500").Find(What:=.Range("r1")).Activate
If FoundCell Is Nothing Then
MsgBox "not found"
Else
FoundCell.Activate
End If
End With


End Sub

how do I get rid of the error?