Arrays - declaration, adding values to arrays and calculation

M

Maxi

Where can I find easy or step-by-step instructions on understanding
arrays?

I am a VBA beginner and would like to learn declaring arrays and using
it. I have read a lot of articles and help files but terms like ReDim,
Preserve, Subscripts, Resize, Parameter arrays, Dynamic/Fixed size
arrays etc. are going over my head.

The only thing that I have understood is that the first argument
represents the rows; the second represents columns. Currently I want to
learn how to declare arrays and how to transfer values once it is
declared.

How can I do the following

I want to declare three arrays BigData(), SmallData() and FinalData()
BigData() will have 9 rows and 20 columns. I think the answers is Dim
BigData(8, 19) As Integer but I am not sure whether it is right or
wrong.
SmallData() will have 15 rows and 10 columns. I think the answers is
Dim SmallData(14, 9) As Integer but I am not sure whether it is right
or wrong.
FinalData() will have 10 columns but rows will be dynamic. I don't know
how to declare this.

For instance, lets say BigData has the following values (I don't know
how to transfer these values to the declared array)
3,4,7,9,10,11,21,32,33,35,37,41,47,57,60,64,69,72,74,75
4,6,9,10,12,13,15,21,22,31,35,47,49,52,56,63,64,72,74,75
4,6,9,10,15,21,31,33,34,41,42,45,46,47,57,60,68,72,74,78
3,4,6,10,11,13,18,21,30,32,33,35,46,53,60,67,69,74,77,78
6,9,13,16,21,22,31,46,48,49,52,61,63,64,69,70,71,75,78,79
3,4,7,10,14,17,18,21,28,31,33,36,37,43,47,57,65,69,75,80
4,7,13,15,17,25,29,32,37,42,45,47,50,57,60,64,68,71,72,74
3,7,10,11,16,18,28,34,35,43,47,51,52,55,56,57,60,64,71,72
8,9,10,12,16,21,22,28,38,47,49,51,52,53,54,55,64,66,71,72

and SmallData has the following values (again I don't know how to
transfer these values to the declared array)
3,4,7,10,21,33,37,47,57,69
3,4,10,11,21,32,33,35,60,69
3,7,10,11,35,47,57,60,64,72
3,10,11,21,32,33,35,60,69,74
4,6,9,10,15,21,31,47,72,74
4,7,32,37,47,57,60,64,72,74
4,9,10,21,33,41,47,57,60,72
4,10,21,33,41,47,57,60,72,74
6,9,13,21,22,31,49,52,63,64
6,13,21,22,31,49,52,63,64,75
9,10,21,33,41,47,57,60,72,74
9,13,21,22,31,49,52,63,64,75
13,15,17,25,29,32,37,42,45,47
34,35,43,47,51,52,55,56,57,60
49,51,52,53,54,55,64,66,71,72

I want to search the first data in SmallData()
3,4,7,10,21,33,37,47,57,69 in all the data in BigData() and check how
many numbers matches. 10 numbers matches in first row, 4 in 2nd row, 6
in 3rd and 4th, 2 in 5th, 10 in 6th, 5 in 7th and 8th and 3 in the 9th
row. If 10 numbers matches in more than or equal to 2 rows (here it
satisfies the condition) then transfer this data
(3,4,7,10,21,33,37,47,57,69) in the first row of FinalData(). Move to
the 2nd row of SmallData() and do the same thing. Do it 15 times for
all the 15 rows in SmallData().

Once it is done, we will have 12 rows in FinalData(). The following
three will be eliminated because all 10 numbers matches only once.
13,15,17,25,29,32,37,42,45,47
34,35,43,47,51,52,55,56,57,60
49,51,52,53,54,55,64,66,71,72

Once every thing is done, liste these 12 rows in the worksheet.
 
T

Tom Ogilvy

Your declarations for bigdata and small data are fine, but probably
irrelevant.

Is the data on a spreadsheet? Then to put it in an array, you would do

Dim bigdata, smalldata
bigdata = Range("A1").Resize(9,20).Value
smalldata = Range("AA1").Resize(15,10)

If you want to make final data dynamic, then you can only make the last
dimension dynamic. You describe making the first dimension dynamic. This
isn't allowed if you want to preserve the existing data which I am sure you
do. What you need to do is change you thinkg for finaldata and rotate it 90
degrees so it columns in the first dimension and rows in the second. You
would just make you code consistent with this orientation

Redim Preserve FinalData(1 to 10,1 to ubound(FinalData,2)+1)
for j = 1 to 10
finaldata(j,rw) = Smalldata(rw1,j)
Next j

for example.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top