Code to output arrays to Excel spreadsheet is affecting prior iterations

Question

I'm attempting to print small arrays to an Excel spreadsheet. The bulk of the code to loops n times based on the user's discretion.

The output Sub functions print correctly on the first iteration, but when the array changes on the next iteration and the sub functions move to the next line to output, they also modify the first array values in the spreadsheet from the first iteration.

Example: If I go through five iterations, and they all produce different values in their respective arrays, by the 5th iteration All five columns that have been printed are modified to be the same as the last iteration.

I'm trying to prevent the code from replacing previous values.

I've attempted the Erase function for the array inside of the big for loop, which broke the code.

For loop for iterations

Dim Iter As Integer

For Iter = 1 To number_of_iterations
    Randomize
    reward_present = Int((1 - 0 + 1) * Rnd + 0)
    reward_string = reward_present
    reward_present = 1
    'Randomize whether there is a reward present or not
    
    If reward_present = 1 Then
        Dim door_probabilities() As Variant
        ReDim door_probabilities(1 To number_of_doors)
        Dim remainder As Double
        Dim reward_door As Integer
        Dim reward_door_string As String
        
        
        remainder = 1
        For i = 1 To (number_of_doors - 1)
            door_probabilities(i) = RndDbl(0, remainder)
            remainder = remainder - door_probabilities(i)
        Next i
        door_probabilities(number_of_doors) = remainder
        'randomizing probabilities of each door

        Dim max As Variant
        max = door_probabilities(1)
        reward_door = 0
        For i = 1 To number_of_doors
            If max <= door_probabilities(i) Then
                max = door_probabilities(i)
                reward_door = i
            End If
        Next i
        reward_door_string = reward_door
        'choosing the reward door based on probability

        If number_of_doors = 3 Then
            random_player_choice = Int((number_of_doors - 1 + 1) * Rnd + 1)
            game_doors(random_player_choice) = 1
            ArrayFillPlayer1 game_doors, Iter
            
            'choose first player door randomly
            'output here
            
            For i = LBound(game_doors) To UBound(game_doors)
                msg = msg & game_doors(i) & vbNewLine
            Next i
            MsgBox "Game doors player choice 1: " + msg
            
            random_host_choice = Int((number_of_doors - 1 + 1) * Rnd + 1)
            Do While random_host_choice = random_player_choice
                random_host_choice = Int((number_of_doors - 1 + 1) * Rnd + 1)
            Loop
            If random_host_choice = reward_door Then
                Do While random_host_choice = reward_door
                    random_host_choice = Int((number_of_doors - 1 + 1) * Rnd + 1)
                Loop
            End If
                
            game_doors(random_host_choice) = 1
            ArrayFillHost game_doors, Iter
            
            'choose host door randomly
            'output here
            
            For i = LBound(game_doors) To UBound(game_doors)
                msg = msg & game_doors(i) & vbNewLine
            Next i
            MsgBox "Game doors host choice: " + msg

            random_player_choice2 = Int((number_of_doors - 1 + 1) * Rnd + 1)
            Do While random_player_choice2 = random_host_choice
                random_player_choice2 = Int((number_of_doors - 1 + 1) * Rnd + 1)
            Loop
            game_doors(random_player_choice2) = 1
            'choose second player door


            ArrayFillPlayer2 game_doors, Iter
            For i = LBound(game_doors) To UBound(game_doors)
                msg = msg & game_doors(i) & vbNewLine
            Next i

            ReDim game_doors(1 To number_of_doors)             

        End If

Sub ArrayFillPlayer1(TempArray As Variant, RowToWrite As Integer)
   'Fill a range by transferring an array
    Dim CellsDown As Long, CellsAcross As Long
    Dim TheRange As Range

    CellsDown = 1
    CellsAcross = 3

    'Cells.Clear

    'Set worksheet range
    Set TheRange = Range(Cells(RowToWrite, 1), Cells(CellsDown, CellsAcross))

   Transfer temporary array to worksheet
    TheRange.Value = TempArray

End Sub
Sub ArrayFillHost(TempArray As Variant, RowToWrite As Integer)
   'Fill a range by transferring an array
    Dim CellsDown As Long, CellsAcross As Long
    Dim TheRange As Range

    CellsDown = 1
    CellsAcross = 6

    'Cells.Clear

    'Set worksheet range
    Set TheRange = Range(Cells(RowToWrite, 4), Cells(CellsDown, CellsAcross))

   Transfer temporary array to worksheet
    TheRange.Value = TempArray

End Sub
Sub ArrayFillPlayer2(TempArray As Variant, RowToWrite As Integer)
   'Fill a range by transferring an array
    Dim CellsDown As Long, CellsAcross As Long
    Dim TheRange As Range

    CellsDown = 1
    CellsAcross = 9

    'Cells.Clear

    'Set worksheet range
    Set TheRange = Range(Cells(RowToWrite, 7), Cells(CellsDown, CellsAcross))

   Transfer temporary array to worksheet
    TheRange.Value = TempArray

End Sub

I expect the output of each consecutive row to be different, but they are all modified retroactively.

Answer 1

Kind of looks like you mean to use Resize() in your subs which fill the array to the sheet?

For example this:

Sub ArrayFillPlayer1(TempArray As Variant, RowToWrite As Integer)
   'Fill a range by transferring an array
    Dim CellsDown As Long, CellsAcross As Long
    Dim TheRange As Range

    CellsDown = 1
    CellsAcross = 3

    'Cells.Clear

    'Set worksheet range
    Set TheRange = Range(Cells(RowToWrite, 1), Cells(CellsDown, CellsAcross))

   Transfer temporary array to worksheet
    TheRange.Value = TempArray

End Sub

Here the line:

Set TheRange = Range(Cells(RowToWrite, 1), Cells(CellsDown, CellsAcross))

is the same as:

Set TheRange = Range( Cells(RowToWrite, 1), Range("C1") )

and that "C1" remains constant as RowToWrite increases, so each time you're filling a larger range with TempArray.

This is closer to what you want:

Sub ArrayFillPlayer1(TempArray As Variant, RowToWrite As Integer)
   Range(Cells(RowToWrite, 1).Resize(1, 3).Value = TempArray
End Sub