Writing many arrays to spreadsheet using .Offset and .Resize syntax

[DonEB]

I am using a program that was designed to create combinations of players, tally up some specific values, sum up those values and then write them to the spreadsheet once completed. That part of the program continues to work just fine. Now, I'm trying to add one more element to this program and can't get it to write to the spreadsheet correctly.

In short, I've created a new array called array_MaxPlayT. Based on my running "debug", I know that I am appropriately populating the array with the expected values. My problem seems to be getting the correct output written to the spreadsheet. Example: using DEBUG, I am able to tell that array_MaxPlayT(1) = 22, array_MaxPlayT(2) = 22, array_MaxPlayT(3) = 34, array_MaxPlayT(4) = 26, array_MaxPlayT(5) = 22, etc. However, only the value of 22 get written to the spreadsheet for each line and I don't understand why.

The code I'm using is below. The new code I inserted is highlighted in BOLD. Again, the original code still seems to work fine. I don't understand why the new array is only writing the one/same value for all combinations.

Any thoughts on how to make this work correctly would be appreciated.

'  Following code sorts chart from top to bottom
    Dim ws2 As Worksheet
    Set ws2 = Worksheets("CommonData")

'  Following code sorts chart from left to right
    ws2.Sort.SortFields.Clear
    ws2.Range("E3:X24").Sort Key1:=ws2.Range("E3:X3"), Order1:=xlAscending, Orientation:=xlLeftToRight

    Dim lCombinations() As Long, lPairs() As Long, lNumbers() As Long, N As Long, r As Long, i As Long, j As Long, lOffset As Long
    Dim dScores() As Double
    Dim inarray As Variant, NameList As Variant, [B]array_MaxPlayT As Variant[/B]

    N = 20
    r = 4
    lOffset = 7  'Col A --> Col H for scores
    lPairs = GetCombinations(r, 2)
    If UBound(lPairs) > lOffset Then
        MsgBox "You need a bigger offset!"
        Exit Sub
    End If
    NameList = Sheets("CommonData").Range("$E$3:$X$3").Value2                 'Places only the Player Numbers into NAMELIST
    lCombinations = GetCombinations(N, r)
[B]    ReDim array_MaxPlayT(1 To UBound(lCombinations)) As Integer[/B]
    ReDim dScores(1 To UBound(lCombinations), 1 To UBound(lPairs))
    ReDim arrFreqPlay(1 To UBound(lCombinations), 1 To UBound(lPairs))
    ReDim lNumbers(1 To UBound(lCombinations), 1 To r)
    inarray = Sheets("CommonData").Range("E5:X24").Value2
    arrFreqPlay = Sheets("CommonData2").Range("E6:X25").Value2 
    
    For i = 1 To UBound(lCombinations)
        For j = 1 To UBound(lPairs)
            dScores(i, j) = arrFreqPlay(lCombinations(i, lPairs(j, 1)), lCombinations(i, lPairs(j, 2)))
            
[B]            If Max1 > 0 Then
                If dScores(i, j) = Max1 Then
                    MaxTotal = MaxTotal + 10
                End If
            End If     ' If Max1 > 0
        
            If Max2 < Max1 Then
                If dScores(i, j) = Max2 Then
                    MaxTotal = MaxTotal + 7
                End If
            End If      'If Max2 < Max1 Then

            If Max3 < Max2 Then
                If dScores(i, j) = Max3 Then
                    MaxTotal = MaxTotal + 3
                End If
            End If              'If Max3 < Max2 Then[/B]
        Next j

        For j = 1 To r
            lNumbers(i, j) = NameList(1, lCombinations(i, j))
        Next j
 
[B]        array_MaxPlayT(i) = MaxTotal                 'Place cumulative MaxTotal for potential group into array
        MaxTotal = 0                                           'Reset MaxTotal to 0 in prep of examining next set of players[/B]
    Next i

    With Worksheets(wsName).Range("C7").Resize(UBound(lCombinations))    'modify sheet name as appropriate
        .Resize(, r).Value = lNumbers
[B]        .Offset(, lOffset - 1).Resize(, UBound(lPairs)).Value = array_MaxPlayT[/B]                                 'NOTE:  I did IOffset - 1 because I wasn't sure how to modify the existing code to receive this new array
        .Offset(, lOffset).Resize(, UBound(lPairs)).Value = dScores
        .Offset(, lOffset + UBound(lPairs)).FormulaR1C1 = "=SUM(RC[-" & UBound(lPairs) & "]:RC[-1])"
        .Resize(, lOffset + UBound(lPairs) + 1).Sort Key1:=.Columns(lOffset + UBound(lPairs) + 1), Order1:=xlAscending
    End With

 

[Peter_SSs]

The new code I inserted is highlighted in BOLD.

To do that (Bold), you need to use the RICH code tags, not the VBA code tags.

 

[DonEB]

To do that (Bold), you need to use the RICH code tags, not the VBA code tags.

View attachment 92414

Thanks for the information Peter. I noticed that my attempt to BOLD the new code lost when I used the VBA code tags but also noticed that all new code was preceded by and ended with which reflected where I had BOLDED my code. (example below)

ReDim array_MaxPlayT(1 To UBound(lCombinations)) As Integer

Is it possible to use both VBA code tags and Rich code tags at the same time? I thought wrapping the code in VBA code tags to reflect that it was VBA code was more important. Sorry if I made the incorrect choice.

In the meantime, I remain hopeful that someone will be able to provide some kind of guidance with my original inquiry/issue/problem. I continue to do some research to try and resolve this myself but I've had no success at this time.

 

[Alex Blakenburg]

Can you put the 1st line below before the 2nd line shown and check if what you see in the immediate window makes sense.
PS: If you want to mark up the code use RICH instead of VBA.

Debug.Print "lPairs: " & UBound(lPairs), "  Address: " & .Offset(, lOffset - 1).Resize(, UBound(lPairs)).Address  ' Add this line
.Offset(, lOffset - 1).Resize(, UBound(lPairs)).Value = array_MaxPlayT

 

[DonEB]

Can you put the 1st line below before the 2nd line shown and check if what you see in the immediate window makes sense.
PS: If you want to mark up the code use RICH instead of VBA.

Debug.Print "lPairs: " & UBound(lPairs), "  Address: " & .Offset(, lOffset - 1).Resize(, UBound(lPairs)).Address  ' Add this line
.Offset(, lOffset - 1).Resize(, UBound(lPairs)).Value = array_MaxPlayT

I believe I found the solution to my problem. I had read online that by default VBA populates an array into a line. To put the values of the array in a column, the array must be transposed. Therefore, I replaced the code I have in BOLD below with the following and now the expected results gets written to the worksheet as I had hoped.
.Offset(, lOffset - 1).Resize(, UBound(lPairs)).Value = Application.WorksheetFunction.Transpose(array_MaxPlayT)

With Worksheets(wsName).Range("C7").Resize(UBound(lCombinations))    'modify sheet name as appropriate
        .Resize(, r).Value = lNumbers
        .Offset(, lOffset - 1).Resize(, UBound(lPairs)).Value = array_MaxPlayT
        .Offset(, lOffset).Resize(, UBound(lPairs)).Value = dScores
        .Offset(, lOffset + UBound(lPairs)).FormulaR1C1 = "=SUM(RC[-" & UBound(lPairs) & "]:RC[-1])"
        .Resize(, lOffset + UBound(lPairs) + 1).Sort Key1:=.Columns(lOffset + UBound(lPairs) + 1), Order1:=xlAscending
    End With

Using another worksheetfunction is not a preference of mine since I'm trying to make my program more efficient. So, if there is a different, more efficient way to accomplish the task of allowing me to write this array to a column within my worksheet, please let me know. I'm always trying to learn something new.

Thanks,
Don

 

[Alex Blakenburg]

Even professional developers will use the Transpose function but it does cap out at 65,536 items.

In your case since you are loading it in the code and you don't need to resize the array in the code, you can simply define it as 2 dimensional with only 1 column and then you won't need to transpose it.

    ' Dim as 2 dimensional array
    ReDim array_MaxPlayT(1 To UBound(lCombinations), 1 To 1) As Integer
   
    ' Populate it as 2 dimensional array
    array_MaxPlayT(i, 1) = MaxTotal

 

[DonEB]

Thank you. That worked!!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.