find the index number for an array element

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Guest]

I meant a VBA array. Thanks, Tom.

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.

 

[Peter Bernadyne]

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

 

[unavailable]

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]

That was very helpful, thank you very much.

 

[Peter Bernadyne]

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

 

[Alan Beban]

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]

I see what you mean.

Thanks again