Excel 2000 Macro not changing value in cell

Posted by Simon on February 28, 2001 6:17 PM

Please help me.
I want the macro to look up a cell and use the value in the find command. It works fine the first time, when I'm recording the macro, but when I'm running the macro again, It uses the old value, and not the new value in the reference cell.

I'm looking forward to hear from sombody.

Simon

Posted by David Hawley on February 28, 2001 6:28 PM

Hi Simon

when using Find in VBA you MUST specify each time what to search for, else it will keep using the last value. Try this code.

Sub TryThis()
Dim LookFor As String

With Sheets("Sheet1")
LookFor = .Range("A1")
.Cells.Find(What:=LookFor, _
After:=.Cells(2, 1)).Select
End With
End Sub

Dave

OzGrid Business Applications