Find Value in Array and report on Location

  • Thread starter Thread starter jC!
  • Start date Start date
J

jC!

hi all,

trying to lookup a value in an array (using FIND) and if the value is
found, the location (Row and Column) of the found value to be
reported.

example=
A1: 3
A2: 4
A3: 5
A4: 6
A5: 7

this is where i am up to=
Sub findLocation()
Dim r As Range
If Not Worksheets("Sheet1").Range("A:A").Find(what:="5", _
LookIn:=xlValues, _
lookat:=xlWhole) Is Nothing Then

Set r = Worksheets("Sheet1"). _
Cells(Worksheets("Sheet1"). _
Range("A:A"). _
Find(what:="5", _
LookIn:=xlValues, _
lookat:=xlWhole).Row, "A")
End If
End Sub

the result that i receive=

r = 5 (but i would like to receive A3 (or$A$3))

any help is mostly appreciated and i thank you in advance for your
time.

cheers....


.....jurgenC!
 
hi Rebert,

thanks for your help and this is what i ended up with (not too sure if
this is what you were refering too):

Sub findLocation()
Dim r As Range
If Not Worksheets("Sheet1").Range("A:A").Find(what:="5", _
LookIn:=xlValues, _
LookAt:=xlWhole) Is Nothing Then

Set r = Worksheets("Sheet1"). _
Cells(Worksheets("Sheet1"). _
Range("A:A"). _
Find(what:="5", _
LookIn:=xlValues, _
LookAt:=xlWhole).Row, "A")
Debug.Print r.Address
End If
End Sub
 
If Not Worksheets(1).Range("A:A").Find(5, , , xlWhole) Is Nothing Then
Set r = Worksheets(1).Range("A:A").Find(5, , , xlWhole)
end if
Msgbox r.Address

Alan Beban
 
Just a different idea.

Sub Demo()
Dim addr As String
On Error Resume Next
With [A1:A5]
addr = .Item(WorksheetFunction.Match(5, Range(.Address),
0)).Address(False, False)
End With
End Sub

It returns "" if no match is found.
 
Back
Top