Solved: Locating Address without Selecting cell

[mferrisi]

Is there a way determine the location of a cell that contains a value, such as with

Range("A16:V20") Find(What:= "Boise" , LookAt:= xlWhole, etc ect .Activate

row# = row.activecell
column# = column.activecell

but without the cell actually getting selected on the sheet?

Thanks

[Norie]

Try something like this.

Set rngFind = Range("A16:V20") Find(What:= "Boise" , LookAt:= xlWhole)

If Not rngFind Is Nothing Then Msgbox rngFind.Address

[Simon Lloyd]

Try this:

Sub findit()
Dim FndCell
Dim firstAddress As String
With Sheets("Sheet1").Range("A1:B20")
Set FndCell = Cells.Find(What:="Boise", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not FndCell Is Nothing Then
firstAddress = FndCell.Address
Do
MsgBox FndCell.Address
Set FndCell = .FindNext(FndCell)
Loop While Not FndCell Is Nothing And FndCell.Address <> firstAddress
End If

End With

End Sub

Regards,
Simon