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!

If the functions in the downloadable file at
http://home.pacbell.net/beban are available to your workbook

arr = ArrayMatch("Fish", MyArray)
MsgBox Application.Index(MyArray, arr(1, 1), arr(1, 2) + 1)

This will return the value from the "column" of the array immediately to
the "right" of the first occurrence of "Fish" in MyArray.

Alan Beban

Brent McIntyre wrote:
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!

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!

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!

Can you help me expand on this idea..
What I need to do is to read a column of values (could be upto 300
rows) and depending on its value, place the values from the adjacent 4
columns in locations on other worksheets...

So the idea is :

[A] [b] [C] [D] [E]
[1] 10002 SH1 B64 209.00 0.00
[2] 10035 SH1 C38 0.00 104.00
[3] 10056 SH2 B82 100.00 0.00
[4] 10083 SH3 C38 0.00 104.00
[5] 10124 SH3 C38 0.00 101.00

AND IF POSSIBLE, If you notice, I have the last 2 lines heading to
same field..
then I wouldl like to add them together.. but that I will work around
if this makes it "not possible" or too difficult.

So basically I have to iterate through 300 rows and depending on the
value in Col A (or B.. or C.. not sure which col it could come in.. so
the col assignment has to be dynamic).. it will be the left most
column for sure..
then to take the 4 columns next to the col with the values and
populate the location specified in Col 2 and 3 combined.

This is urgent.. I have been going in circles..
Thanks.
Baba












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!

Baba wrote:

"then I wouldl like to add them together."

Add *what* together?

If the functions in the file at http://home.pacbell.net are available,
and if SH1,SH2, etc. are actual Worksheet names, the following will work:

Dim arr() As Variant, ws As Worksheet, rng As Range, i As Integer
arr = VLookups(10035, Range("tbl2"), _
Application.Evaluate("{2,3,4,5}"))
Set ws = Worksheets(arr(1, 1))
Set rng = ws.Range(arr(1, 2))
For i = 1 To 4
rng(1, i).Value = arr(1, i)
Next

It populates not only the location specified in the address specified in
Columns 2 & 3, but also of course the next three cells to the right.

Or if someone can furnish the corresponding VBA syntax for the
equivalent VLOOKUP formula, that can be substituted for the VLookups
function above.

Alan Beban

Can you help me expand on this idea..
What I need to do is to read a column of values (could be upto 300
rows) and depending on its value, place the values from the adjacent 4
columns in locations on other worksheets...

So the idea is :

[A] [b] [C] [D] [E]
[1] 10002 SH1 B64 209.00 0.00
[2] 10035 SH1 C38 0.00 104.00
[3] 10056 SH2 B82 100.00 0.00
[4] 10083 SH3 C38 0.00 104.00
[5] 10124 SH3 C38 0.00 101.00

AND IF POSSIBLE, If you notice, I have the last 2 lines heading to
same field..
then I wouldl like to add them together.. but that I will work around
if this makes it "not possible" or too difficult.

So basically I have to iterate through 300 rows and depending on the
value in Col A (or B.. or C.. not sure which col it could come in.. so
the col assignment has to be dynamic).. it will be the left most
column for sure..
then to take the 4 columns next to the col with the values and
populate the location specified in Col 2 and 3 combined.

This is urgent.. I have been going in circles..
Thanks.
Baba












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!

Tom and Alan,

Thank you very much for all your help it is greatly appreciated by those
of us who are searching for answers. I am always encouraged by the help
I receive from these newsgroups, especially Tom who has helped me many
times.

Keep up the good work !

Yours sincerely,

Brent McIntyre

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

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!

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!

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!

After some help from Tom Ogilvy, here is code that doesn't depend on the
downloadable file of functions:

Dim arr() As Variant, ws As Worksheet, rng As Range
arr = Evaluate("VLookup(10035, tbl2, {2,3,4,5},False)")
Set ws = Worksheets(arr(1))
Set rng = ws.Range(arr(2))
Range(rng(1, 1), rng(1, 4)).Value = arr

Alan Beban

Alan Beban wrote:
Baba wrote:

"then I wouldl like to add them together."

Add *what* together?

If the functions in the file at http://home.pacbell.net are available,
and if SH1,SH2, etc. are actual Worksheet names, the following will work:

Dim arr() As Variant, ws As Worksheet, rng As Range, i As Integer
arr = VLookups(10035, Range("tbl2"), _
Application.Evaluate("{2,3,4,5}"))
Set ws = Worksheets(arr(1, 1))
Set rng = ws.Range(arr(1, 2))
For i = 1 To 4
rng(1, i).Value = arr(1, i)
Next

It populates not only the location specified in the address specified in
Columns 2 & 3, but also of course the next three cells to the right.

Or if someone can furnish the corresponding VBA syntax for the
equivalent VLOOKUP formula, that can be substituted for the VLookups
function above.

Alan Beban

Can you help me expand on this idea.. What I need to do is to read a
column of values (could be upto 300
rows) and depending on its value, place the values from the adjacent 4
columns in locations on other worksheets...

So the idea is :

[A] [b] [C] [D] [E]
[1] 10002 SH1 B64 209.00 0.00
[2] 10035 SH1 C38 0.00 104.00 [3] 10056 SH2
B82 100.00 0.00 [4] 10083 SH3 C38 0.00 104.00
[5] 10124 SH3 C38 0.00 101.00
AND IF POSSIBLE, If you notice, I have the last 2 lines heading to
same field..
then I wouldl like to add them together.. but that I will work around
if this makes it "not possible" or too difficult.

So basically I have to iterate through 300 rows and depending on the
value in Col A (or B.. or C.. not sure which col it could come in.. so
the col assignment has to be dynamic).. it will be the left most
column for sure..
then to take the 4 columns next to the col with the values and
populate the location specified in Col 2 and 3 combined.

This is urgent.. I have been going in circles..
Thanks.
Baba












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!

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!

Tom, rather than leave you speechless, let me submit the following.
Let's call the one that loops one column less "the abbreviated form",
and the other one "the full form". They both return the same thing
unless the sought value (sStr) can and does appear only in the
"rightmost column" of MyArray, which I assume will not happen in the
OP's application. But even if that assumption is incorrect, in that
case the abbreviated form runs without error (leaving res empty) and the
full form returns a Subscript out of range error(leaving res empty). So
which is desirable depends on which of those two results is desired in
that case.

What thinking about this pointed out for me is that both forms probably
need to provide an error message if res remains empty; otherwise, when
the sought value doesn't appear in the array at all, nothing would be
returned but no error message would advise of that. If this thinking is
correct, the only difference between the two forms would be *which*
error message was returned in the case that the sought value appeared
only in the "rightmost column" of MyArray.

By the way, the original code I suggested, which depends on the array
functions from my website, was

arr = ArrayMatch("Fish", MyArray)
MsgBox Application.Index(MyArray, arr(1, 1), arr(1, 2) + 1)

This also has an abbreviated form that searches all but the last
"column" of MyArray

arr = ArrayMatch("Fish", SubArray(MyArray, 1, 2, 1, 3))
MsgBox Application.Index(MyArray, arr(1, 1), arr(1, 2) + 1)

But both the full form and the abbreviated form return a Type mismatch
error when sought value does not appear in MyArray before the last
"column".

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!

Sorry... I got pulled away from the post and did not know how to get
back to it.. is there some way to bookmark it with using IE bookmarks?


I tried the code.. but it says..
"Compile error:

Can't assign to array"


and it highlights the "arr= Evalu...."

What am i missing?

Thanks in advance for all your help.
Ali






Alan Beban wrote in message ...
After some help from Tom Ogilvy, here is code that doesn't depend on the
downloadable file of functions:

Dim arr() As Variant, ws As Worksheet, rng As Range
arr = Evaluate("VLookup(10035, tbl2, {2,3,4,5},False)")
Set ws = Worksheets(arr(1))
Set rng = ws.Range(arr(2))
Range(rng(1, 1), rng(1, 4)).Value = arr

Alan Beban

Alan Beban wrote:
Baba wrote:

"then I wouldl like to add them together."

Add *what* together?

If the functions in the file at http://home.pacbell.net are available,
and if SH1,SH2, etc. are actual Worksheet names, the following will work:

Dim arr() As Variant, ws As Worksheet, rng As Range, i As Integer
arr = VLookups(10035, Range("tbl2"), _
Application.Evaluate("{2,3,4,5}"))
Set ws = Worksheets(arr(1, 1))
Set rng = ws.Range(arr(1, 2))
For i = 1 To 4
rng(1, i).Value = arr(1, i)
Next

It populates not only the location specified in the address specified in
Columns 2 & 3, but also of course the next three cells to the right.

Or if someone can furnish the corresponding VBA syntax for the
equivalent VLOOKUP formula, that can be substituted for the VLookups
function above.

Alan Beban

Can you help me expand on this idea.. What I need to do is to read a
column of values (could be upto 300
rows) and depending on its value, place the values from the adjacent 4
columns in locations on other worksheets...

So the idea is :

[A] [b] [C] [D] [E]
[1] 10002 SH1 B64 209.00 0.00
[2] 10035 SH1 C38 0.00 104.00 [3] 10056 SH2
B82 100.00 0.00 [4] 10083 SH3 C38 0.00 104.00
[5] 10124 SH3 C38 0.00 101.00
AND IF POSSIBLE, If you notice, I have the last 2 lines heading to
same field..
then I wouldl like to add them together.. but that I will work around
if this makes it "not possible" or too difficult.

So basically I have to iterate through 300 rows and depending on the
value in Col A (or B.. or C.. not sure which col it could come in.. so
the col assignment has to be dynamic).. it will be the left most
column for sure..
then to take the 4 columns next to the col with the values and
populate the location specified in Col 2 and 3 combined.

This is urgent.. I have been going in circles..
Thanks.
Baba












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!

Post the code you tried.

Alan Beban

Baba wrote:
Sorry... I got pulled away from the post and did not know how to get
back to it.. is there some way to bookmark it with using IE bookmarks?


I tried the code.. but it says..
"Compile error:

Can't assign to array"


and it highlights the "arr= Evalu...."

What am i missing?

Thanks in advance for all your help.
Ali






Alan Beban wrote in message ...

After some help from Tom Ogilvy, here is code that doesn't depend on the
downloadable file of functions:

Dim arr() As Variant, ws As Worksheet, rng As Range
arr = Evaluate("VLookup(10035, tbl2, {2,3,4,5},False)")
Set ws = Worksheets(arr(1))
Set rng = ws.Range(arr(2))
Range(rng(1, 1), rng(1, 4)).Value = arr

Alan Beban

Alan Beban wrote:

Baba wrote:

"then I wouldl like to add them together."

Add *what* together?

If the functions in the file at http://home.pacbell.net are available,
and if SH1,SH2, etc. are actual Worksheet names, the following will work:

Dim arr() As Variant, ws As Worksheet, rng As Range, i As Integer
arr = VLookups(10035, Range("tbl2"), _
Application.Evaluate("{2,3,4,5}"))
Set ws = Worksheets(arr(1, 1))
Set rng = ws.Range(arr(1, 2))
For i = 1 To 4
rng(1, i).Value = arr(1, i)
Next

It populates not only the location specified in the address specified in
Columns 2 & 3, but also of course the next three cells to the right.

Or if someone can furnish the corresponding VBA syntax for the
equivalent VLOOKUP formula, that can be substituted for the VLookups
function above.

Alan Beban


Can you help me expand on this idea.. What I need to do is to read a
column of values (could be upto 300
rows) and depending on its value, place the values from the adjacent 4
columns in locations on other worksheets...

So the idea is :

[A] [b] [C] [D] [E]
[1] 10002 SH1 B64 209.00 0.00
[2] 10035 SH1 C38 0.00 104.00 [3] 10056 SH2
B82 100.00 0.00 [4] 10083 SH3 C38 0.00 104.00
[5] 10124 SH3 C38 0.00 101.00
AND IF POSSIBLE, If you notice, I have the last 2 lines heading to
same field..
then I wouldl like to add them together.. but that I will work around
if this makes it "not possible" or too difficult.

So basically I have to iterate through 300 rows and depending on the
value in Col A (or B.. or C.. not sure which col it could come in.. so
the col assignment has to be dynamic).. it will be the left most
column for sure..
then to take the 4 columns next to the col with the values and
populate the location specified in Col 2 and 3 combined.

This is urgent.. I have been going in circles..
Thanks.
Baba












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!