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

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

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

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

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

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

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

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

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