Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the index number for an array element
I want to lookup/find a specific value in an array and return that values
index number. In this case, the lookup list is the first column of 3 column array. Suggestions? DM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the index number for an array element
Do you mean a VBA array or a range of cells on a worksheet
for a VBA array: Dim v as Variant, i as Long, idx as Long v = Range("A1:C200") for i = 1 to 200 if v(i,1) = "dog" then idx = i exit for end if Next if idx < 0 then msgbox "found at index " & idx else msgbox "Not found" end if for a worksheet, use the match worksheet function. -- Regards, Tom Ogilvy "Dick Minter" wrote in message ... I want to lookup/find a specific value in an array and return that values index number. In this case, the lookup list is the first column of 3 column array. Suggestions? DM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the index number for an array element
I meant a VBA array. Thanks, Tom.
"Tom Ogilvy" wrote: Do you mean a VBA array or a range of cells on a worksheet for a VBA array: Dim v as Variant, i as Long, idx as Long v = Range("A1:C200") for i = 1 to 200 if v(i,1) = "dog" then idx = i exit for end if Next if idx < 0 then msgbox "found at index " & idx else msgbox "Not found" end if for a worksheet, use the match worksheet function. -- Regards, Tom Ogilvy "Dick Minter" wrote in message ... I want to lookup/find a specific value in an array and return that values index number. In this case, the lookup list is the first column of 3 column array. Suggestions? DM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the index number for an array element
You know, it's funny, I would have thought that there would be a more efficient way of identifying an array element instead of having to resort to a loop, but I suppose there isn't. That said, is there anyway of referring to just 1 column of a multiple column array such as the one described? By this, I mean the entire range of values in that column, of course. So, if I had an array of (1 to 50, 1 to 3) for instance, is there a way of referencing all 50 elements in column 1, by any chance? I'm trying to get something like myarray(:,1) to indicate the first column (as in Matlab, per any Matlab users out there). Thanks, -Pete -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=504031 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the index number for an array element
Watch for word wrap
Sub test1() Dim arr arr = Range("A1:C50") arr1 = Application.Index(arr, 0, 1) '1st column arr2 = Application.Index(arr, 0, 2) '2nd column arr3 = Application.Index(arr, 0, 3) '3rd column Debug.Print Application.Index(Application.Index(arr, 0, 2), 2, 1) '2nd row 2nd column Debug.Print Application.Index(Application.Index(arr, 0, 1), 3, 1) '3rd row 1st column End Sub Alan Beban Peter Bernadyne wrote: You know, it's funny, I would have thought that there would be a more efficient way of identifying an array element instead of having to resort to a loop, but I suppose there isn't. That said, is there anyway of referring to just 1 column of a multiple column array such as the one described? By this, I mean the entire range of values in that column, of course. So, if I had an array of (1 to 50, 1 to 3) for instance, is there a way of referencing all 50 elements in column 1, by any chance? I'm trying to get something like myarray(:,1) to indicate the first column (as in Matlab, per any Matlab users out there). Thanks, -Pete |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the index number for an array element
That was very helpful, thank you very much. -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=504031 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the index number for an array element
By the way, is it possible to perform an operation on 2 vectors such as arr1 and arr2 in this example? For instance, would it be possible to create: arr_result = arr1 - arr2 consisting of the differences between each individual element of arr1 & arr2 all in one fell swoop or do you have to loop through such a thing? Any advice much welcomed. -Pete -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=504031 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the index number for an array element
If
Sub test1() Dim arr arr = Range("a1:c3") arr1 = Application.Index(arr, 0, 1) arr2 = Application.Index(arr, 0, 2) arr_result = ArrayAdd(arr1, arr2, False) End Sub The looping is built in to the function. Alan Beban Peter Bernadyne wrote: By the way, is it possible to perform an operation on 2 vectors such as arr1 and arr2 in this example? For instance, would it be possible to create: arr_result = arr1 - arr2 consisting of the differences between each individual element of arr1 & arr2 all in one fell swoop or do you have to loop through such a thing? Any advice much welcomed. -Pete |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the index number for an array element
I see what you mean. Thanks again -- Peter Bernadyn ----------------------------------------------------------------------- Peter Bernadyne's Profile: http://www.excelforum.com/member.php...nfo&userid=701 View this thread: http://www.excelforum.com/showthread.php?threadid=50403 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX & MATCH find first column value in an array? | Excel Worksheet Functions | |||
How to "return" the array element number in VBA | Excel Discussion (Misc queries) | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
VBA array - find largest element | Excel Programming |