Forum Discussion

sf49ers19238597's avatar
sf49ers19238597
Iron Contributor
Oct 14, 2024
Solved

I need help with an error in vba code

I need help with an error in the VBA code.   I have a user form that has scores. I also have a bye week worksheet that has weeks when teams bye. The bye weeks worksheet looks at user form and t...
  • sf49ers19238597's avatar
    sf49ers19238597
    Oct 15, 2024

    I fix my error problem.

     

    Private Sub cmbweeks_Change()
    Dim Ws As Worksheet
    Dim WsBye As Worksheet
    Dim R As Long
    Dim C As Long
    Dim I As Long
    Dim TEAMS As String
    
    Set Ws = Worksheets("INPUT_SCORES")
    Set WsBye = Worksheets("BYE WEEKS")
    NFL_SCORES_FORM.txtbills.SetFocus
    
    With Me
        'SENDING TO FORM             2 to 33 ROWS
    
        'AFC EAST TEAMS
        .txtbills.Value = Ws.Cells(2, Me.cmbweeks.Value + 1)
        .txtdolphins.Value = Ws.Cells(3, Me.cmbweeks.Value + 1)
        .txtjets.Value = Ws.Cells(4, Me.cmbweeks.Value + 1)
        .txtpatriots.Value = Ws.Cells(5, Me.cmbweeks.Value + 1)
    
        'AFC NORTH TEAMS
        .txtbengals.Value = Ws.Cells(6, Me.cmbweeks.Value + 1)
        .txtbrowns.Value = Ws.Cells(7, Me.cmbweeks.Value + 1)
        .txtravens.Value = Ws.Cells(8, Me.cmbweeks.Value + 1)
        .txtsteelers.Value = Ws.Cells(9, Me.cmbweeks.Value + 1)
    
        'AFC SOUTH TEAMS
        .txtcolts.Value = Ws.Cells(10, Me.cmbweeks.Value + 1)
        .txtjaguars.Value = Ws.Cells(11, Me.cmbweeks.Value + 1)
        .txttexans.Value = Ws.Cells(12, Me.cmbweeks.Value + 1)
        .txttitans.Value = Ws.Cells(13, Me.cmbweeks.Value + 1)
    
        'AFC WEST TEAMS
        .txtbroncos.Value = Ws.Cells(14, Me.cmbweeks.Value + 1)
        .txtchargers.Value = Ws.Cells(15, Me.cmbweeks.Value + 1)
        .txtchiefs.Value = Ws.Cells(16, Me.cmbweeks.Value + 1)
        .txtraiders.Value = Ws.Cells(17, Me.cmbweeks.Value + 1)
    
        'NFC EAST TEAMS
        .txtcommanders.Value = Ws.Cells(18, Me.cmbweeks.Value + 1)
        .txtcowboys.Value = Ws.Cells(19, Me.cmbweeks.Value + 1)
        .txteagles.Value = Ws.Cells(20, Me.cmbweeks.Value + 1)
        .txtgiants.Value = Ws.Cells(21, Me.cmbweeks.Value + 1)
    
        'NFC NORTH TEAMS
        .txtbears.Value = Ws.Cells(22, Me.cmbweeks.Value + 1)
        .txtlions.Value = Ws.Cells(23, Me.cmbweeks.Value + 1)
        .txtpackers.Value = Ws.Cells(24, Me.cmbweeks.Value + 1)
        .txtvikings.Value = Ws.Cells(25, Me.cmbweeks.Value + 1)
    
        'NFC SOUTH TEAMS
        .txtbuccaneers.Value = Ws.Cells(26, Me.cmbweeks.Value + 1)
        .txtfalcons.Value = Ws.Cells(27, Me.cmbweeks.Value + 1)
        .txtpanthers.Value = Ws.Cells(28, Me.cmbweeks.Value + 1)
        .txtsaints.Value = Ws.Cells(29, Me.cmbweeks.Value + 1)
    
        'NFC WEST TEAMS
        .txt49ers.Value = Ws.Cells(30, Me.cmbweeks.Value + 1)
        .txtcardinals.Value = Ws.Cells(31, Me.cmbweeks.Value + 1)
        .txtrams.Value = Ws.Cells(32, Me.cmbweeks.Value + 1)
        .txtseahawks.Value = Ws.Cells(33, Me.cmbweeks.Value + 1)
    
        'BYE's for the selected week
        R = Int((Val(.cmbweeks.Value) - 1) / 6) * 8 + 1
        C = (Int((Val(.cmbweeks.Value) - 1) * 3) + 2) Mod 18
    
       For I = 1 To 6
            R = R + 1
            If Not IsEmpty(WsBye.Cells(R, C)) Or Len(WsBye.Cells(R, C)) > 0 Then
                TEAMS = Trim(WsBye.Cells(R, C).Value)
                
                ' Print the value of TEAMS for debugging
                Debug.Print "TEAMS: " & TEAMS
                
                ' Print the control name for debugging
                Debug.Print "Control Name: " & "txt" & TEAMS
                On Error Resume Next
                .Controls("txt" & TEAMS).Value = "BYE"
                If Err.Number <> 0 Then
                    Debug.Print "Error: " & Err.Description
                    Err.Clear
                End If
                On Error GoTo 0
            End If
        Next I
        End With
    End Sub

Resources