Find Value in Array and report on Location
 

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!

Find Value in Array and report on Location
 

Use:

..Address instead of .Row

--
_______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel


"jC!" wrote in message
om...
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!

Find Value in Array and report on Location
 

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find Value in Array and report on Location
 

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

jC! wrote:
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!

Find Value in Array and report on Location
 

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.
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"jC!" wrote in message
om...
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!

Find Value in Array and report on Location
 

hi Alan,

thank you very much for your elegant solution

cheers....

....jurgenC!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find Value in Array and report on Location
 

My pleasure . . . Cheers!

jurgenC! wrote:
hi Alan,

thank you very much for your elegant solution

cheers....

...jurgenC!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!