PDA

View Full Version : Solved: Locating Address without Selecting cell



mferrisi
03-30-2007, 11:43 AM
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
03-30-2007, 11:51 AM
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
03-30-2007, 12:12 PM
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