Setting Unknown Array Boundaries and Loop

Question

I am having trouble with the boundaries of an array. I have 3 worksheets. The first 2 have i convert into two arrays (array1 & array2), and then do calculations between them to create the third. The issue is I am not sure what the boundaries are of the third array, since it will always change depending on the inputs.

I have been just using Dim array3 (5000, 5) with a dummy of 5000 rows (1st dimension) since I don't think there will be more than that. Is there a way to create the array with out boundaries and then add info to it, then dim the dimensions?

Also the Macro I created utilizes this piece of code ---

     Z = 1
     For x = 1 To UBound(array1, 1)

     For y = 1 To UBound(array2, 1)

      If array1(x, 4) = 0 Then
        GoTo Line1
        End If

        If array1(x, 1) = array2(y, 1) And array1(x, 2) = array2(y, 3)Then

            If array1(x, 4) > array2(y, 5) Then

                array3(z, 1) = array1(x, 3)

            ElseIf array1(x, 4) = array2(y, 5) Or array1(x, 4) < array2(y, 5) Then

                array3(z, 1) = array1(x, 3)

            End If

        z = z + 1

     End If

    Next y

Line1:
Next x

It takes a piece of array1 and loops it through array2 and creates a result in array3

Basically when array1(x, 4) = 0, I need it to move on to the next X. I can't figure out how to loop this without the GoTO Line1. If i move it down, then it will continue to loop through arry2 (y), instead of moving on to next X. If i move it above, then y resets and it runs through the For y loop again

score 1 · Accepted Answer · answered Feb 18 '18 at 17:05

you could do that:

dimming array3 to the theoretically maximum rows number
transposing array3 back and forth and redim its rows number to the actually filled ones

like per this (commented) code:

    Dim x As Long, y As Long, z As Long
    Dim array1 As Variant, array2 As Variant

    array1 = ... ' your way of filling array1
    array2 = ... ' your way of filling array2

    ReDim array3(LBound(array1, 1) To UBound(array1, 1) * UBound(array2, 1), LBound(array1, 1) To LBound(array1, 2) ' dim Array 3 to the theoretically maximum number of rows and to the wanted columns number (here, the same as array1 columns

    z = LBound(array3, 1) - 1 'start from array3 rows number lower bound minus one to update at every matching criteria
    For x = LBound(array1, 1) To UBound(array1, 1)

        For y = LBound(array2, 1) To UBound(array2, 1)
            If array1(x, 4) <> 0 Then
                If array1(x, 1) = array2(y, 1) And array1(x, 2) = array2(y, 3) Then
                    z = z + 1
                    If array1(x, 4) > array2(y, 5) Then
                        array3(z, 1) = array1(x, 3)
                    Else
                        array3(z, 1) = array2(x, 3) ' see my guess here instead of your original code
                    End If
                End If
            End If
        Next
    Next

    If z >= 0 Then
        array3 = Application.Transpose(array3) 'transpose array3
        ReDim Preserve array3(LBound(array1, 1) To LBound(array1, 1) + 1, LBound(array1, 1) To z) 'redim its columns to their actually filled number, while preserving values
        array3 = Application.Transpose(array3) 'trasnpose back your array3
    End If

score 0 · Answer 2 · answered Feb 18 '18 at 02:39

Yes you can! There a multiple ways to arrive at this!

You can easily just Redim PReserve the array. try looking at:

What does ReDim Preserve do?

https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/redim-statement

Keep in mind you can only redim the last dimension of the array with this method. If you need to alter others, you'll need to learn how to alter your array to do so. HEre is another great link

ReDim Preserve to a Multi-Dimensional Array in Visual Basic 6

You could also grab the row and column counts of the first two sheets before processing. That way you can use this information later.

Try this:

 THisworkbook.sheets("sheet1").USedRange.rows.count
 THisworkbook.sheets("sheet1").USedRange.columns.count

Setting Unknown Array Boundaries and Loop

2 Answers2

Linked