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

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



"**** Minter" <DickMinter@discussions.microsoft.com> wrote in message
news:11A83F44-1887-4AED-963B-E4457301742A@microsoft.com...
> 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

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
>
>
>
> "**** Minter" <DickMinter@discussions.microsoft.com> wrote in message
> news:11A83F44-1887-4AED-963B-E4457301742A@microsoft.com...
> > 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
>
>
>

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

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
>
>

That was very helpful, thank you very much.