Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Location in Array | Excel Discussion (Misc queries) | |||
Finding the Location within an Array | Excel Worksheet Functions | |||
Finding Location of Maximum Value in 2D Array | Excel Discussion (Misc queries) | |||
Finding Location of Maximum Value in 2D Array | New Users to Excel | |||
Finding Location of Maximum Value in 2D Array | Excel Worksheet Functions |