Trying to use a for loop to cycle through variables in a class in VBA

Matthew Valenzuela 0
Viewed 26

I want to use kind of loop to go through each game variable in the class of clsPlayerPicks. Not sure how to replace the number in font of game with a variable or if it is even possible.

Class clsPlayerPicks

Public Name As String
Public Game1 As String
Public Game2 As String
Public Game3 As String
Public Game4 As String
Public Game5 As String
Public Game6 As String
Public Game7 As String
Public Game8 As String
Public Game9 As String
Public Game10 As String
Public Game11 As String
Public Game12 As String
Public Game13 As String
Public Game14 As String
Public Game15 As String
Public Game16 As String
Public MNS As Long

{
Sub GetPlayerRanking(Games, PlayerRank, PlayerPicks As Collection)

    Dim Game As clsGames
    Dim Ranks As clsPlayerRank
    Dim Picks As clsPlayerPicks
    Dim x, Week, GameNum As Long
    
    'Fill Player Name in Ranks and Picks
    For x = 1 To Sheet6.Cells(Rows.Count, 1).End(xlUp).Row Step 20
    
        Set Ranks = New clsPlayerRank
        Set Picks = New clsPlayerPicks
        
        Ranks.Name = Sheet6.Cells(x, 1)
        Picks.Name = Sheet6.Cells(x, 1)
        
        For Week = 1 To 18
        
            For GameNum = 1 To 16
            
                Picks.Controls("Game" & GameNum) = Sheet6.Cells(Week + x, GameNum + 1)
            
            Next GameNum
        
        Next Week
        
    Next x

End Sub
}
vba
1 Answers

Use arrays

Public Game(1 To 16) As String

And loop like

Dim i As Long
For i = LBound(Game) To UBound(Game)  ' 1 To 16
    Debug.Print Game(i)
Next i
Pᴇʜ0
Related