find the index number for an array element

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dick Minter - 23 Jan 2006 15:20 GMT

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

Reply to this Message

Tom Ogilvy - 23 Jan 2006 15:32 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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

Reply to this Message

Dick Minter - 23 Jan 2006 15:42 GMT

I meant a VBA array. Thanks, Tom.

> Do you mean a VBA array or a range of cells on a worksheet
>
[quoted text clipped - 22 lines]
> >
> > DM

Reply to this Message

Peter Bernadyne - 10 Mar 2006 16:48 GMT

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

Signature

Peter Bernadyne

Reply to this Message

unavailable - 10 Mar 2006 19:59 GMT

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

> 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
[quoted text clipped - 12 lines]
>
> -Pete

Reply to this Message

Peter Bernadyne - 13 Mar 2006 17:17 GMT

That was very helpful, thank you very much.

Signature

Peter Bernadyne

Reply to this Message

Peter Bernadyne - 13 Mar 2006 19:40 GMT

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

Signature

Peter Bernadyne

Reply to this Message

Alan Beban - 15 Mar 2006 21:11 GMT

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

> 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
[quoted text clipped - 8 lines]
>
> -Pete

Reply to this Message

Peter Bernadyne - 21 Mar 2006 00:01 GMT

I see what you mean.

Thanks again

Reply to this Message