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!