how to get the index for a two diemnstional array..

[shishi]

Hi all,

I have a two dimensional array. I populated this array as a variant
by using 3 columns and 30 rows of data. It is as below..

Name1 cde 20
Name2 pdf 30
Name3 xlf 20
Name 4 ppf 20
Name5 xdf 40
and so on..

I read this in to a variant array as below.

Sub GenerateSumArray()
Dim sumCell As Range

Set sumCell =
ThisWorkbook.Sheets("SUMMARY").Range("START_SUMMARY").Offset(1, 2)

SummaryArray = Application.Transpose(Range(sumCell,
sumCell.End(xlDown).End(xlToRight)))

End Sub

Now I wish to use data from this SummaryArray. First I need to find out
the index for the "Name5" and then get the corresponding values..So do
we have any built in function like indexof(SummaryArray("Name5")) and
then use that index to get its values(xdf and 40) or do you have a
better suggestion..Thanks in advance...
shishi

 

[Tom Ogilvy]

I assume the data you depict is how it appears in the worksheet and the
array has been transposed so the names are in the first row of the array
rather than the first column.

Dim idx as Long, i as Long

idx = -5
for i = 1 to ubound(summaryarray,2)
if summaryarray(1,i) = "Name5" then
idx = i
exit for
end if
Next
if idex >=0 then
msgbox "Name5" & " " & summaryarray(2,idx) & _
" " & summaryarray(3,idx)
Else
msgbox "Name5 not found"
End if