Array Formula

[ub]

Hi
I have an array in my vba code. Values from range from 5 different sheets
are saved in these arrays

the code is
Dim r1 as variant
Dim r2 as variant
Dim r3 as variant
Dim r4 as variant
Dim r5 as variant

r1 = worksheet("Sheet1").Range("a2:g2").Value
r2 = worksheet("Sheet2").Range("a2:g2").Value
r3 = worksheet("Sheet3").Range("a2:g2").Value
r4 = worksheet("Sheet4").Range("a2:g2").Value
r5 = worksheet("Sheet5").Range("a2:g2").Value

I want to consolidate these 5 arrays into 1 array .
Can someone advise me how can I do this in my VBA code

 

[Joel]

Dim MyRange(5)

Set MyRange(0) = worksheet("Sheet1").Range("a2:g2").Value
Set MyRange(1) = worksheet("Sheet2").Range("a2:g2").Value
Set MyRange(2) = worksheet("Sheet3").Range("a2:g2").Value
Set MyRange(3) = worksheet("Sheet4").Range("a2:g2").Value
Set MyRange(4) = worksheet("Sheet5").Range("a2:g2").Value

 

[Joel]

I thought you were lookig for the ranges not the values

Dim r(5) as variant
Dim AllData() as Variant

set r(1) = worksheet("Sheet1").Range("a2:g2")
set r(2) = worksheet("Sheet2").Range("a2:g2")
set r(3) = worksheet("Sheet3").Range("a2:g2")
set r(4) = worksheet("Sheet4").Range("a2:g2")
set r(5) = worksheet("Sheet5").Range("a2:g2")

ItemCount = 0
for i = 1 to 5
for each cell in r(i)
ItemCount = ItemCount + 1
redim AllData(ItemCount)
AllData(Itemcount) = cell.value
next cell
next i

 

[Dave Peterson]

If Joel's response doesn't do what you want...

What does consolidate mean?

Do you want a single 5 x 1 x 7 array?
(sheet, row, column)

Do you want a single 1 x 7 array
(sum or concatenate strings of the corresponding values (row, column))

or a single 5 x 7
(one row per sheet by 7 column)

 

[ub]

Hi Joel

Thanks for the reply
I am getting an error.
My cell value in colum G of every sheet has large text ( Can have upto 600
char) and I have about 500 rows in each sheet. So my range in each sheet is
A2:G500 (it can go upto G1000). When I use this code, the array stores value
upto certain range only and then it gives out of range error. But If I don't
add column "G" in my range, it is OK
Please advise, how can I correct this

Thanks

 

[ub]

Hi Dave
Thanks for your reply.
Data in the 5 sheets of my workbook is filled by 5 different user(Teams)
through userform
TEAM 1-5 (all have same info)
Column A - Name
Column B - Date
Column C - ID
Column D to M have results
Column N has comments (Could be about 600 - 800 char)

What I mean by consolidate is that I can count the valus in D to M using the
name that match my criteria and then count results for each team seperatly
for a given period of time as per criteria
The challenge is that a user can be in any sheet. So I want to look for the
result of the user is all sheets.
That is what I meant by consolidating.
But I think 3 dimensional array would work great, but I don't know how to do
it.
Your assistance , will be greatly apprecaited.

Thanks

 

[Dave Peterson]

Before you do anything with arrays (in VBA, right???), think of consolidating
your data into a single worksheet.

Add a new column A (shifting all the data to the right one column) that would be
used as the team indicator.

Then you could add headers in Row 1, select the entire range and do
Data|Filter|autofilter.

You'll be able to filter to show the information that you want (team or name or
date or any combination)--or hide the information that you don't want to see.

====
Just a note. If you're using xl2002 or newer, you can use Find to search for
anything within the workbook.

In earlier versions (actually any version), you could use Jan Karel Pieterse's
FlexFind:
http://www.oaltd.co.uk/MVP/

But life will be much easier if you put the data on a single sheet. Filtering,
sorting, charts, pivottables all become easier.

 

[ub]

Hi Dave
Currently all my data goes in one consolidated sheet that is shared between
5 users
But often I get Reslove Conflict error that I am unable to handle in my VBA
code
If I can handle Reslove Conflict error to send message to the user that the
data was not saved and he has to reenter, my problem will be solved.
Or else I was planing to feed data from each team in 5 differetn sheets and
then consolidate the data in one array to get the out put.
But the data for each team is huge approx 1000 rows and 14 columns
Can you suggest any better solution.

Thanks

 

[ub]

Hi Dave
Currently the data from all 5 user is feeded in 1 sheet that is a shared
workbook.
But often we get a Reslove Conflict - Shared workbook error.
I am not able to handle this error so that I can advise the user who's data
was not saved to resubmit the data.
That is why I thought to feed data in 5 different sheets and then
consolidate it.
Please advise if you have any other suggestion

 

[Dave Peterson]

There are instructions how to handle those conflicts in Excel's help.

I searched for "resolve conflicts" and got a couple of options. But I think it
depends on who you should trust.

But I've never used a shared workbook in real life. Lots of very smart people
in the newsgroups will recommend never using them. (Stories of shared workbooks
becoming corrupted.)

You may want to use an application that was meant for simultaneous access by
your users--maybe Access or any other database program????

 

[ub]

Thanks Dave
You gave a good advise.
If I don't go with shared workbook and my data is in 5 diffrent sheets, then
what will be best solution to consolidate the data.
The option I though was to call a procedure that will get data from all 5
sheets with sheet index in an array and then use that array for all my
calculation.
Please guide me , how can I do this.
Also, if I do this will it be possible to trace the row# of the record and
the name of the sheet.
Ex: If I have all the information in one array and based on my criteria I
found a match in the array element. Can I somehow trace the row# and the
sheet where the value is store. Ex my array element (10,2) meets my criteria
and it is stored in sheet 4 in row#10.

 

[Dave Peterson]

You mean 5 different workbooks--not just 5 different sheets in the same
workbook, right?

If yes, the Ron de Bruin shares lots of sample code here:
http://www.rondebruin.nl/tips.htm
Look for:
Copy/Paste/Merge examples