PDA

View Full Version : VBA Find based on cell value



chrisrob
12-06-2007, 05:26 AM
Hi,

I am trying to do the following...
I would like to run a macro to find data in a compltete workbook based on what the value of a certain cell is.
Eg lets say A1=200, then search the whole workbook for 200 and goto that cell.

Many thanks.

Bob Phillips
12-06-2007, 05:42 AM
Dim sh As Worksheet
Dim cell As Range

For Each sh In ActiveWorkbook.Worksheets

Set cell = sh.Cells.Find(200)
If Not cell Is Nothing Then

Application.Goto cell
Exit For
End If
Next sh

chrisrob
12-06-2007, 05:49 AM
Thanks xld - that does the trick.....but what if I want the data in A1 to be variable - so each time I run the macro i could have pasted a different valu into A1 and it will search for that specific value ??

Thanks again.

Bob Phillips
12-06-2007, 09:38 AM
Dim sh As Worksheet
Dim cell As Range

For Each sh In ActiveWorkbook.Worksheets

If sh.Name <> Activesheet.Name Then

Set cell = sh.Cells.Find(Range("A1").Value)
If Not cell Is Nothing Then

Application.Goto cell
Exit For
End If
End If
Next sh

chrisrob
12-08-2007, 12:02 AM
Thanks xld - exactly what I needed !