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...

BYE WEEK AND INPUT SCORES WORKSHEETS.xlsb52 KB

excel

Macros and VBA

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

Forum Discussion

I need help with an error in vba code

Resources