Dynamic Array Data Population (Self Sizing Condition)

[vbsjive]

Hi!

I have gone through lots of websites trying to find a solution to a particular problem I'm having. I work in a Call center where I need to keep track of and combine several reports that are different sizes in terms of rows and colums. I have figured out a way through Hlook up to alighn the data just how I want it, but now face a new problem. Jeeze I hope I can explain this.

I would like to populate an array of which the row bounds and column bounds are determined by a cell/s IE: Cell A1 = 7 (a Dynamic value) and A2 = 8 (also Dynamic).

Here's where it becomes very complicated for me because the ultimate goal of this self-sizing array is to examine all possible combinations of addition for two given ranges of values. For example:

If a particular range is populated with: 1,blank,blank,4,blank,6,7

And the 2nd range is populated with: 0,blank,blank,3,4,blank,blank,7,8

then I want to populate the array as: 1+0, 1+3,1+4,1+7,1+8.........until the last iteration 7+0,7+3,7+4,7+7,7+8 Ends the routine.

The tricky part also for "blank" cell values (which aren't actually "blank" and are filled with an "IF formula" in both the above ranges) is that I want them skipped over. So if a Cell is blank then no addition is performed, but also when populating the array I want that geographic position to remain blank in the array. And cells that are not blank I want to add the data and put the result in the same place as well; The 2nd range determines placement in the array.

Retaining a continuous symmetry to the array is critical, as "blank cells" are integral to the examining results.

Ultimately the data that will be displayed is evey combination of addition. While the above example suggests that there are 20 combinations of addition, the actual dimension of the array that is displayed in the worksheet would be larger because of the blank spaces, and its size I believe would be 7X9.

Thanks for any help offered. I'm Stumped!

Michael

 

[MickG]

Based on your 2 ranges in rows (1) (7 cells) & row(2) (9 cells)
Try this to return your matrix in (A10:I7), to see if it giving you the right start to solving your problem.
Your Data:-

[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(A) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(B) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(C) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(D) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(E) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(F) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(G) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(H) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(I) [/B][/COLOR]
1.      1                       4               6       7                      
2.      0                       3       4                       7       8

[COLOR="Navy"]Sub[/COLOR] MG03Mar38
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] ray(1 To 7, 1 To 9)
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng1 = Range("A1:A8")
        [COLOR="Navy"]Set[/COLOR] Rng2 = Range("A2:A9")
[COLOR="Navy"]For[/COLOR] Rw = 1 To 7
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 9
        ray(Rw, Ac) = IIf(Rng1.Cells(1, Rw) = "" Or Rng2.Cells(1, Ac) = "", "", Rng1.Cells(1, Rw) + Rng2.Cells(1, Ac))
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Rw
Range("A10:I16") = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[vbsjive]

MickG

First time I ran it I got a type Mismatch.

Then I got "Can't Execute Code in Break Mode"

And again after a 3rd time: Type Mismatch.

However, the most important aspect is that I did get to see the VBA line of logic and the subsequent go through your use of IFF. What you posted is pretty much the start of exactly what I'm trying to accomplish!

Thank you for your help!

 

[MickG]

Try this:-
Open you sheet and place values in rows 1 & 2 as per my previous thread.
To insert code:-
This is a double click event on cell "A1"
Right click sheet Tab , Select "View Code) , VB Window appears.
Paste code below into VB Window.
Close VB Window.
To run Code Double click cell "A1".
This is the code:-

Private [COLOR=navy]Sub[/COLOR] Worksheet_BeforeDoubleClick(ByVal Target [COLOR=navy]As[/COLOR] Range, Cancel [COLOR=navy]As[/COLOR] Boolean)
[COLOR=navy]Dim[/COLOR] Rng1 [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Rng2 [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] ray(1 To 7, 1 To 9)
[COLOR=navy]Dim[/COLOR] Rw [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
 [COLOR=navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=navy]Then[/COLOR]
    [COLOR=navy]Set[/COLOR] Rng1 = Range("A1:A7")
        [COLOR=navy]Set[/COLOR] Rng2 = Range("A2:A9")
[COLOR=navy]For[/COLOR] Rw = 1 To 7
    [COLOR=navy]For[/COLOR] Ac = 1 To 9
        ray(Rw, Ac) = IIf(Rng1.Cells(1, Rw) = "" Or Rng2.Cells(1, Ac) = "", "", Rng1.Cells(1, Rw) + Rng2.Cells(1, Ac))
    [COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR] Rw
Range("A10:I16") = ray
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

This is the Result:-

[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(A) [/B][/COLOR][COLOR=royalblue][B]Col(B) [/B][/COLOR][COLOR=royalblue][B]Col(C) [/B][/COLOR][COLOR=royalblue][B]Col(D) [/B][/COLOR][COLOR=royalblue][B]Col(E) [/B][/COLOR][COLOR=royalblue][B]Col(F) [/B][/COLOR][COLOR=royalblue][B]Col(G) [/B][/COLOR][COLOR=royalblue][B]Col(H) [/B][/COLOR][COLOR=royalblue][B]Col(I) [/B][/COLOR]
1.      1                       4       5                       8       9      
2.                                                                             
3.                                                                             
4.      4                       7       8                       11      12     
5.                                                                             
6.      6                       9       10                      13      14     
7.      7                       10      11                      14      15

NB:- The rows are shown as 1 to 7, but are actually 10, 16.
Regards Mick

 

[vbsjive]

MickG

Same problem of a type mismatch.

I followed your exact instructions, opening up a new sheet and the place values to be evaluated in the positions you specify accordingly as a test run.

Type Mismatch occurs at:

ray(Rw, Ac) = IIf(Rng1.Cells(1, Rw) = "" Or Rng2.Cells(1, Ac) = "", "", Rng1.Cells(1, Rw) + Rng2.Cells(1, Ac))

I opened up help to try and identify the nature of the problem, I suspect that for whatever reason, the data types are not compatible.

Yet in your code you define Rw and Ac as intergers. Everything looks good. Are the initial values (manually input) I set in the ranges at A1:A:7 and A2:A:9 not what the routine is expecting thus causing the run-time error?

It seems to be running fine for you. I wonder what the difference is between your workbook/sheet and mine?

No matter, I really appreciate you help. Thanks!

Michael.

 

[MickG]

Have a look at this example , see if this will work.
Dynamic Array.xls
Regards Mick

 

[vbsjive]

Worked Like a charm, Thank you!