ExcelBanter - View Single Post - Multidimensional Arrays

Alan Beban[_3_]

Aw c'mon.

Tom Ogilvy wrote:
I am speechless.

Regards,
Tom Ogilvy

Alan Beban wrote in message
...

Only if sStr were found in the "rightmost column", which I assume is
inconsistent with the OP's desire to return a value from the "right" of
sStr.

Alan Beban

Tom Ogilvy wrote:

In that case, j should only loop to ubound(MyArray,2)-1 or

MyArray(i,j+1)

goes out of bounds.
sStr = "Fish"
For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)-1
If UCase(sStr) = UCase(MyArray(i, j)) Then
res = MyArray(i, j + 1)
Exit For
End If
Next
Next

--
Regards,
Tom Ogilvy

Alan Beban wrote in message
...

Thanks for the mention, Tom.

<This looks only in the second column of your array.

This is the limitation that caused me to suggest the ArrayMatch
function, although the following avoids it as well:

sStr = "Fish"
For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
If UCase(sStr) = UCase(MyArray(i, j)) Then
res = MyArray(i, j + 1)
Exit For
End If
Next
Next

Alan Beban

Tom Ogilvy wrote:

Alan has written a lot of code to manipulate arrays and generously

provided

it and shown you how to use it in this situation. However, if,

perhaps,

this is a learning exercise and you want to use a simple loop to find

the

value you could do

sStr = "Fish"
for i = lbound(myarray,1) to ubound(myarray,1)
if ucase(sStr) = Ucase(myarray(i,Lbound(myarray,2)+1)) then
res = myArray(i,Lbound(myarray,2)+2)
exit for
end if
Next
msgbox res

This looks only in the second column of your array.

Regards,
Tom Ogilvy

"Brent McIntyre" wrote in

message

. ..

Tom,

Thanks very much for your help, but I think I have confused everyone,
including myself.

What I want to do is create a virtual table via an array of 151 rows

and

6 columns.

I have worked out the whole thing of writing in the array, ie setting

up

the virtual table, but I am not sure how to access it, I need to be

able

to get a certain value, check whether it appears in a certain column

and

if it does display the information from another column.

ie

Virtual Table
One Cat Apartment
Two Fish House
Three Cow Flat

And if the read in value is "Fish" I want it to output "House"

I hope this makes it all a bit more clear.

Thank you all for your help it is much appreciated, this is my first
time using Multidimensional Arrays.

Yours sincerely,

Brent McIntyre

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!