Procedure too Large - Array calculation and Helper Array?

[GeeWhiz7]

I am encountering an error I don't know how to fix. My gut says I need a helper array (new to arrays in general) to achieve this, but maybe someone has a clever alternative.

Info: 'b' array gets its values from division of two array elements in 'a' array

The code below works fine unless I increase the number of calculations and then I get a Procedure too large error. My dataset needs to generate around 1200 columns in array b using this approach. I haven't figured out the syntax to do all the calculations with a loop.

Goal: Be able to make up to 1200 calculations like b(i,1) = a(i,1) / a(i,2) without running into Procedure too large error

Option Explicit

Sub test()
  Dim lr As Long, a As Variant, b As Variant
  Dim i As Long, j As Long
  
  lr = Sheet3.Range("A" & Rows.Count).End(xlUp).row

  a = Sheet2.Range("B2:AY" & lr).Value2

  ReDim b(1 To UBound(a, 1), 1 To 10)
    
'##Nested loop to get cell values from array a to array b with a calculation in between
  
  For i = 1 To UBound(a)

'    For j = 1 To 50  ## not using j at the moment

        b(i, 1) = a(i, 1) / a(i, 2) 'note these calculations are representing non recursive combinations of 5 elements. ie any non repeating combinations of 1 through 5 using two #s.
        b(i, 2) = a(i, 1) / a(i, 3) ' I can't figure out how to do this with a loop
        b(i, 3) = a(i, 1) / a(i, 4) ' maybe I need some sort of helper array as if I simply put in more calculations like this (~1200)
        b(i, 4) = a(i, 1) / a(i, 5) ' eventually the procedure becomes too large even for 64bit.
        b(i, 5) = a(i, 2) / a(i, 3)
        b(i, 6) = a(i, 2) / a(i, 4)
        b(i, 7) = a(i, 2) / a(i, 5)
        b(i, 8) = a(i, 3) / a(i, 4)
        b(i, 9) = a(i, 3) / a(i, 5)
        b(i, 10) = a(i, 4) / a(i, 5)
    'Next

  Next
  Range("B2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  
  
End Sub

 

[johnnyL]

Eyes on your own paper @kevin9999

I think the latest postings include a wider spread of the J = 1 to 50, instead of the 1 to 5 that wasn't included in the first postings. Just my thinkings at this point.

 

[kevin9999]

Eyes on your own paper @kevin9999

I think the latest postings include a wider spread of the J = 1 to 50, instead of the 1 to 5 that wasn't included in the first postings. Just my thinkings at this point.

I see (said the blind man) so =COMBIN(50,2) evaluates to 1225 - which is probably where the 1200 columns comes from...

 

[GeeWhiz7]

I'm really struggling to reconcile the description of the formulas above, with your description of the (only) 10 possible combinations you described in post #6.

I hear you, it’s hard for me to describe. Let’s look at it his way, instead.

• The complete code being used is in post #1, except I have stripped out more than 1000 rows that calculate the values to go into the b array. The shortened code works as desired and it is when I add back in the other 1000 that it errors.
• The formulas for the b array are taking the ratio of certain columns in the a array so that the each iteration is a different permutation of non repeating combinations of the a array headers.
• So in the example, A, B, and C can have three unique combinations. A/B would b the first calculated column in array b. Then A/C, Then B/C.
• The number of columns in the b array once complete would be equal to the ‘total combinations’ (3 in example). In the real dataset this is 50 (like a, b, c but more) inputs and somewhere around 1176 total combinations which I thought I could get away with pasting each individual formula for their corresponding b in there.
• the only thing in the formulas that are changing in a not easy to replicate with a loop fashion are the rows/columns from the a array.

so since I have the rows and columns needed from the earlier effort I thought I could put them in a separate array to ’help’ a single formula in the For loop choose the right rows/columns to get from array a.

This is like my second week with arrays, so here we are. Maybe just an example of how to use an array to help another select non sequential columns or something would be enough for me to get it.

 

[johnnyL]

This is making my head hurt so I am going to throw this out there for now:

Sub test()
'
    Dim ArrayColumn         As Long, ArrayRow   As Long
    Dim ColumnStartNumber   As Long
    Dim lr                  As Long
    Dim TotalColumns        As Long, TotalRows  As Long
    Dim a                   As Variant, b       As Variant
'
    TotalColumns = 50
    TotalRows = 0
    lr = Sheet3.Range("A" & Rows.Count).End(xlUp).Row
'
    a = Sheet2.Range("B2:AY" & lr).Value2
    ReDim b(1 To UBound(a, 1), 1 To TotalColumns)
'
'##Nested loop to get cell values from array a to array b with a calculation in between
'
    For ArrayRow = 1 To UBound(a)
        For ColumnStartNumber = 2 To TotalColumns
            For ArrayColumn = ColumnStartNumber To TotalColumns
                TotalRows = TotalRows + 1
                b(ArrayRow, TotalRows) = a(ArrayRow, ColumnStartNumber - 1) / a(ArrayRow, ArrayColumn)
            Next
        Next
    Next
'
    Range("B2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

 

[GeeWhiz7]

This is making my head hurt so I am going to throw this out there for now:

Sub test()
'
    Dim ArrayColumn         As Long, ArrayRow   As Long
    Dim ColumnStartNumber   As Long
    Dim lr                  As Long
    Dim TotalColumns        As Long, TotalRows  As Long
    Dim a                   As Variant, b       As Variant
'
    TotalColumns = 50
    TotalRows = 0
    lr = Sheet3.Range("A" & Rows.Count).End(xlUp).Row
'
    a = Sheet2.Range("B2:AY" & lr).Value2
    ReDim b(1 To UBound(a, 1), 1 To TotalColumns)
'
'##Nested loop to get cell values from array a to array b with a calculation in between
'
    For ArrayRow = 1 To UBound(a)
        For ColumnStartNumber = 2 To TotalColumns
            For ArrayColumn = ColumnStartNumber To TotalColumns
                TotalRows = TotalRows + 1
                b(ArrayRow, TotalRows) = a(ArrayRow, ColumnStartNumber - 1) / a(ArrayRow, ArrayColumn)
            Next
        Next
    Next
'
    Range("B2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

let me try this and will post how it goes tomorrow. my brain passed being able to clearly think about this a few hours ago. seemed simple when I started it and now I find some rowing I didnt expect.
Some sleep and then some coffee and maybe it’ll be clearer.
Thanks for the time you guys have spent pondering this!

 

[johnnyL]

Ok one last try, for now, because I don't like putting out code without 'explaining' my mentality.

I also made a few different changes.

Option Explicit

Sub test()
'
    Dim ArrayColumn             As Long, ArrayRow                   As Long
    Dim ColumnStartNumber       As Long
    Dim InputEndColumnNumber    As Long, InputStartColumnNumber     As Long
    Dim lr                      As Long
    Dim TotalColumns            As Long, TotalRows                  As Long
    Dim InputEndColumnLetter    As String, InputStartColumnLetter   As String
    Dim a                       As Variant, b                       As Variant
'
    InputEndColumnLetter = "AY"                                                         ' <--- Set this to the end column letter of the input range
    InputStartColumnLetter = "B"                                                        ' <--- Set this to the start column letter of the input range
    lr = Sheet3.Range("A" & Rows.Count).End(xlUp).Row
'
    InputEndColumnNumber = Range(InputEndColumnLetter & 1).Column                       ' Calculate InputEndColumnNumber
    InputStartColumnNumber = Range(InputStartColumnLetter & 1).Column                   ' Calculate InputStartColumnNumber
'
    TotalColumns = InputEndColumnNumber - InputStartColumnNumber + 1                    ' Calculate TotalColumns of input
    TotalRows = 0                                                                       ' Initialize TotalRows
'
    a = Sheet2.Range(InputStartColumnLetter & "2:" & InputEndColumnLetter & lr).Value2  ' Save Input values to array 'a'
    ReDim b(1 To UBound(a, 1), 1 To TotalColumns)                                       ' Set # of Columns and rows for array 'b'
'
'##Nested loop to get cell values from array a to array b with a calculation in between
'
    For ArrayRow = 1 To UBound(a)                                                       ' Loop through rows of array 'a'
        For ColumnStartNumber = 2 To TotalColumns                                       '   Incrementaly loop through start columns of array 'a'
            For ArrayColumn = ColumnStartNumber To TotalColumns                         '       Incrementaly loop through current columns
                TotalRows = TotalRows + 1                                               '           Increment TotalRows
                b(ArrayRow, TotalRows) = a(ArrayRow, ColumnStartNumber - 1) / _
                        a(ArrayRow, ArrayColumn)                                        '           Save result into array 'b'
            Next                                                                        '       Loop back
        Next                                                                            '   Loop back
    Next                                                                                ' Loop back
'
    Range("B2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

 

[johnnyL]

All yours now @kevin9999.

 

[GeeWhiz7]

All yours now @kevin9999.

Thank you, ill have to mess with it to make sure I understand it but will report back.

 

[bebo021999]

Never using it before, but I am thinking of 3-D array.

Dim 3Darray()
Redim 3Darray(1 to lastrow, 1 to lastcolumn, 1 to 2)

may help code faster, instead of using multiple 2-D arrays, loop over thousands rows, 50 columns and 2 combinations

Will come back later.

 

[johnnyL]

Never using it before, but I am thinking of 3-D array.

Dim 3Darray()
Redim 3Darray(1 to lastrow, 1 to lastcolumn, 1 to 2)

may help code faster, instead of using multiple 2-D arrays, loop over thousands rows, 50 columns and 2 combinations

Will come back later.

A previous attempt I made for 3D array you might want to consider.

Macro Repeat and sort

Hi All Hope your all doing well :) I need some help with a macro please - I am trying to find a way where after every 4 columns the data is copied and all combined into one until there is no more data Example The Before The After on a new sheet Any help is much appreciated :) thank you

www.mrexcel.com