Forum Discussion
sf49ers19238597
Oct 14, 2024Iron Contributor
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...
- 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
HansVogelaar
Oct 14, 2024MVP
You have declared a variable named team, but you don't assign a value anywhere in your code. So its value is always the empty string "". Is that really what you want?