Vba 1D Nesting 2D array Join

Question

I want to join two 1d array in one 2d with condiditon. Am ı in wrong way?

Conditions are;

If arr2(i) = 6000 Then
    arr3() = Join arr1(i) , arr2(i)
    Next i
Elseif
 arr3() = Join arr1(i), arr2(i), arr2(i+1) when it reach 6000 stop
Next i
bla bla bla...

Here is the results;

Scott Craner · Answer

like I stated loop the second array and test, using variables to store which item in arr1 should be used based on the sum being greater than or equal to 6000:

Sub workForFree()
    Dim arr1 As Variant
    arr1 = Array(1, 2, 3, 4, 5, 6, 7)
    Dim arr2 As Variant
    arr2 = Array(6000, 6000, 6000, 3003, 2003, 3003, 2003, 2003, 1003, 1003, 1003, 1003, 1003, 1003)
    
    Dim sm As Long
    sm = 0
    
    Dim k As Long
    k = 0
    
    Dim outarr As Variant
    ReDim outarr(0 To UBound(arr2), 0 To 1)
    
    Dim i As Long
    
    For i = LBound(arr2) To UBound(arr2)
        If i = LBound(arr2) Then
            k = LBound(arr1)
            sm = arr2(i)
        ElseIf sm + arr2(i) >= 6000 Then
            k = k + 1
            sm = arr2(i)
        Else
            sm = sm + arr2(i)
        End If
        
        outarr(i, 0) = arr1(k)
        outarr(i, 1) = arr2(i)
    Next i
    
         
    ' do something with outarr
    
    ActiveSheet.Range("A1").Resize(UBound(outarr, 1) + 1, 2) = outarr
    
            
End Sub

Vba 1D Nesting 2D array Join

1 Answers