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

DonEB

Board Regular
Joined
Apr 26, 2016
Messages
128
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
'  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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.

1685180849313.png
 
Upvote 0
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.
 
Upvote 0
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.

Rich (BB code):
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
 
Upvote 0
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.

Rich (BB code):
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)

Rich (BB code):
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
 
Upvote 0
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.

VBA Code:
    ' 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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top