my vba code has bugs and errors. please leave comments with how it was fixed and

Question

my vba code has bugs and errors. please leave comments with how it was fixed and a copyable code.

Sub TotalSales2()
    Dim states() As String, reps() As String, repName As String, _
        nStates As Integer, nReps As Integer, newName As Boolean, _
        ws As Worksheet, i As Integer, j As Integer
    nStates = 0
    nReps = 0
    For Each ws In ActiveWorkbook
        ' Add this sheet's state to the States array.
        ReDim states(nStates)
        states(nStates) = ws.Name
        nStates = nStates + 1
      
        ' Go through all of the names in this state's worksheet. For each name, check
        ' if this is a new rep not already in the reps array. If it is, add it to
        ' the array.
        With Worksheets(ws).Range("B3")
            Do Until .Offset(i, 0) = ""
                repName = .Offset(i, 0)
                newName = True
                If nReps > 0 Then
                    For j = 1 To nReps
                        If repName = reps(j) Then
                            newName = False
                            Exit For
                        End If
                    Next
                    If newName = True Then
                        ReDim reps(nReps)
                        reps(nReps) = repName
                        nReps = nReps + 1
                    End If
                End If
            Loop
        End With
    Next
  
    ' Show the user form and capture the user's selections in the variables
    ' selectedState and selectedRep.
    frmInputs.Show
  
   
    ' Find the total sales for the selected rep in the selected state.
    total = 0

  
  
    With Worksheets("SelectedState").Range("B3")
        Do Until .Offset(i, 0) <> ""
            For j = 1 To nReps
                repName = .Offset(i, 0)
                If selectedRep = repName Then
                    total = total + .Offset(i, 1)
                    Exit For
                End If
            Next
        Loop
    End With
  

     ' This message box was missing the & as well as its missing the quote marks in the appendices they need to be there to complete the msgbox
   
    MsgBox "The total sales for " & selectedRep & " in " & "state" & "was " & _
        Format(total, "$#,##0"), vbInformation
End Sub

Explanation / Answer

corrected code :

Sub TotalSales2()
Dim states() As String, reps() As String, repName As String, _
nStates As Integer, nReps As Integer, newName As Boolean, _
ws As Worksheet, i As Integer, j As Integer
nStates = 0
nReps = 0
For Each ws In ActiveWorkbook
' Add this sheet's state to the States array.
ReDim states(nStates)
states(nStates) = ws.Name
nStates = nStates + 1
  
' Go through all of the names in this state's worksheet. For each name, check
' if this is a new rep not already in the reps array. If it is, add it to
' the array.
With Worksheets(ws).Range("B3")
Do Until .Offset(i, 0) = " "
repName = .Offset(i, 0)
newName = True
If nReps > 0 Then
For j = 1 To nReps
If repName = reps(j) Then
newName = False
Exit For
End If
Next
If newName = True Then
ReDim reps(nReps)
reps(nReps) = repName
nReps = nReps + 1
End If
End If
Loop
End With
Next
  
' Show the user form and capture the user's selections in the variables
' selectedState and selectedRep.
frmInputs.Show
  

' Find the total sales for the selected rep in the selected state.
total = 0

  
  
With Worksheets("SelectedState").Range("B3")
Do Until .Offset(i, 0) <> " "
For j = 1 To nReps
repName = .Offset(i, 0)
If selectedRep = repName Then
total = total + .Offset(i, 1)
Exit For
End If
Next
Loop
End With

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.