array

[utreg-4ever]

hi all , here is my problem.
imagine a big excel file with thousands of rows and 5 columns.
First of all, I need only the rows with data when the cell in the first column (A) matches a criterium. (currently i use Autofilter and copypaste the selection into a new sheet)
Secondly, i need to do a calculation for each row using the value in columns B C and D. (currently i put the result in column F)

Then, I need to have the product of the values in the column D and E and sum the result, and divide it by the sum of all values in column E. (currently i use the SUMPRODUCT-functionality). The result of this calculation i need for further purposes.

As i need to do this for several files each day, i already started building a macro, but i am not experienced (e.g. using the recorder). Yet, as the excelfile involves tenthousands of rows, and some other steps that need to be added to the process i described, i feel that i need a smooth VBA-module using arrays to speed it up.

Who could give me advice ? Many thanks in advance for your effort!

[Oorang]

Hi utreg,
Welcome to the board! You gave an excellent description of the process, but I am unclear on something, where is the output going?

[Norie]

This probably could be done with arrays, and that might be quicker.

But I don't see why you couldn't do it other ways.

You can easily use VBA to filter/copy data and insert formulas.

[utreg-4ever]

First of all, thanks to both of you for your interest.

Good points. Well, actually my request is just an isolated part of several manual tasks i'd like to avoid for the future.
Basically, the thousands of rows of data need to be imported from several textfiles (how to do that non-manually with VBA is a question itself, probably posted in another thread soon, ). Then I have to select some values from a separate excelsheet, before i come to the problem i described in my first posting.

That problem isn't limited to only using the autofilter one time, because i have to filter and calculate for several subgroups, which demands for a high-speed code. No doubt that a workaround without the use of arrays (e.g. do .. until , or if ...then , and go through the range row by row) could be a solution, but i prefer an array-solution. Speed becomes even more important if i need to extend the VBA-module to be capable of handling the data for the last 30 days (instead of only 1 day) in 1 shot.

btw, to answer your q, Oorang, the result of the calculation that i described in my first posting needs to be added to a list in another sheet of the same Excel Worksheet as where i import the textfiledata.

Any great ideas and help would be welcome. If any additional info is required to properly advice me, let me know. Thanks!

[Norie]

What makes you think you'd need to go through row by row?

[utreg-4ever]

well, i don't know actually.

[Norie]

well, i don't know actually.

Well neither do I, without further information and/or an idea of your data and what you actually want to achieve.

[mdmackillop]

Welcome to VBAX
Can you post a small typical sample of your data and required output?
Use Manage Attachments in the Go Advanced section.

[Oorang]

Hi utreg, it sounds like you really are just exploring the capabilities of VBA and wanted to see some examples of arrays in action. So with that in mind I will point out some basics. If you already know it, then maybe someone else won't.
The most basic thing is what they are. The easiest way to think about an array is a numbered variable. Instead of having:
[vba]
Dim MyVal1 as String
Dim MyVal2 as String
'etc...
[/vba] You can declare MyVal as an array and refer to it by number.
[vba]
Dim MyVal() as String
for X = 1 to 10
MsgBox MyVal(x)
next
[/vba]
Now as you might or might not know arrays can have one or more dimension. The above example shows a one dimensional array. It's basically a list. A two dimensional array is like a table. So if you had a worksheet loaded into an array, the value of C2 might be accessed like so:
[vba]
MsgBox MyVal(2,3)
[/vba]
As far as I know there is no limit to the number of dimensions you can add. 3 is a cube, it gets more complicated after that, but you could think of 4 dimensions as a list of cubes and 5 as a table of cubes, 6 as a cube of cubes and so on. You will almost never need more than 2 unless you are doing something that belongs in a faster language than VBA
Ok now for the fun stuff... VBA has two types of arrays. Dynamic, and Static. They are just how they sound. A dynamic array can change in size and shape (within limits) and a static array's shape is constant. To declare a static array remember that you always count from zero with arrays (there are exceptions, but this is the general rule) and then just list the dimensions. A two by four table would be declared like so:
[vba]Dim MyVal(1,3) as String[/vba]
Because we are counting from zero you reduce the upperbound of each dimension by one. If you loaded a worksheet into this array cell B1 would be MyVal(0,1).
A dynamic array is just declared with empty parenthesis and receives it's size by manually resizing it with Redim (note that omitting the "Preserve" keyword will wipe out already existing values).
[vba]Dim MyVal() as String
Redim Preserve MyVal(2,3)
[/vba]
After the first redim, only the last dimension can be increased in size.

OK now the above discussion was for "Strongly Typed" arrays. Which is to say any array with a datatype other than "Variant".
Variant arrays are "weakly typed" and therefore slower, but also more flexible. You can you the array function to load them, and manipulate them with the split and join functions. Perhaps the most useful thing for an Excel VBA user is the ability to load a range into a variant array without having to loop through all cells. [vba]Dim MyVal As Variant
MyVal = ActiveSheet.UsedRange
MsgBox MyVal(1, 2)[/vba]

So there is a crash course... Was that at all helpful?
</IMG>

[utreg-4ever]

all, thanks a lot for your quick responses.
mdmackillop, i agree that it is better if i post an example data set. I'll do that after work tonight!

[lucas]

Hey Aaron,
Great brief on arrays....I will be bookmarking this post for reference. Might make a useful article.

[utreg-4ever]

OK, here we go : pls see attached excel file. this also has a small macro. Below the steps i need to go through:

1) only rows are needed that match 'East' in the column A. (so now I use Autofilter first, which is not included in the macro)
2) we need to import some datafields from another excelsheet, which are shown in cells O2:P5
4) calculate the 'WGT AVG' for each row as follows: =(G*I+H*J)/(G+J). (as you can see in the macro, currently I fill column M with this)
3) multiply the value in column E by the appropriate value in the range O2:P5 (e.g. for row 3 where the value in column C is 'RED' you take 3.5). The result is called 'ACTION' (in the macro I let this be placed in column L)
5) I multiply ACTION by WGT AVG for each row. (In the macro I name it CASH). The SUM of this I need to divide by the SUM of all ACTION. (so basically= SUMPRODUCT (M:L) /SUM(M). In the macro I name it NETPOS )
6) for all the rows I need the absolute value of ACTION, and then I take the SUM of that. Which is called CUMACTION.
7) Please note that the columns L and M really don't need to be filled in. The whole purpose of the VBA-code should just be to have values for AVGLEVEL and CUMACTION in the end. Nothing more. These values I use for further calculations….
8) Then…. I need the results of this further calculations to further analyse subsets of the colours (RED, GREEN and BLUE) separately. Here is where I use to filter again and as many times as I have colours (now I have only 3 but this can be as many as 6). For this selections similar calculation needs to be done. I think here the advantage of arrays will prove itself, and that's why was posting my question on array-filling and calculations.

Every bit of time-efficiency that could be realised is welcome as ideally i should let the macro run to process 30 daily datafiles with many many rows at once.

I hope this story is not too confusing?

Oorang, thanks for your very nice explanation about the array-functionality. Maybe you or another nice forummember have some usefull tool for me for this job?

I am grateful for the effort you are taking helping me! thx!

[Oorang]

Hey Aaron,
Great brief on arrays....I will be bookmarking this post for reference. Might make a useful article.

Thanks, I almost added in a word about UDTs or collections, but I thought that might be a bit much for one run rofl.

Utreg post an example with column headers and comments in the code (I know they aren't normally there, but trust me) . I will be happy to refactor it a little for you

[utreg-4ever]

Oorang, thanks for you advice and help. With your postings and some further readings i found my way through my problem. Probably it is not the most efficient piece of code, but hey!
(also found out that some nice merging and filtering of big number of textfiles can be done already in TExtpath before handling the data in Excel)

One thing left though: i would be helped a lot if you could give me advice how to fill an array with only those rows of a big range, where some value in the row (a cell's value or whatever) equals a certain condition.

Something like
row=1
do until row> lastrow of range
If ..=.. then the row is added to the array else not added
row=row+1
loop

Thus, it should be a dynamic array which grows with 1 row each time the condition is met, while going through all rows of a range.
A simple example would be very nice!

Thx.