Last time this question was asked it was wrong! Vba code as bugs in it. Can you

Question

Last time this question was asked it was wrong!

Vba code as bugs in it. Can you please fix the bugs and leave comments to explain the bugs and what was done to fix the bugs with copy code.

The tabs at the bottom are Indiana, Ohio, Illinois, Wisconsin, Michigan.

This is the code I need to use, I need to fix the bugs and comments on how they were fixed. please leave a copy code

Sub ListStates()
    Dim ws As String, message As String
    message = "Here is a list of states:"
    For Each ws In ActiveWorkbook
        message = message & vbCrLf & "ws.Name"
    Next
    MsgBox message, vbInformation, "State list"
End Sub

Sub StateSearch()
    InputBox "Enter a state you'd like to search for.", "State search"
    FindState state, isFound
    MsgBox "State was " & isFound, vbInformation
End Sub

Sub FindState(state As String)
    Dim isFound As Boolean
    For Each ws In ActiveWorkbook
        If ws.Name = state Then
            isFound = True
            Exit For
        Else
            isFound = False
        End If
    Next
End Sub

Sub CountSales()
    Dim state As String, salesRep As String
    state = InputBox "Enter a state"
    salesRep = InputBox "Enter a SalesRep"
    On Error GoTo NoSuchState
    With Worksheets(state).Range("B3")
        Count = 0
        For Each cell In Range(.Offset(1, 0), .End)
            If cell = StateRep Then Count = Count + 1
        Next
    End With
    MsgBox "SalesRep made " Count " sales in " state, vbInformation
NoSuchState:
    MsgBox "The state " state " is not one of the worksheets.", vbInformation
End Sub

Sub TotalSales1()
    Dim lastDate As Date, total As Currency
    MsgBox "You'll now be asked for a date. Then the total of all " _
        " sales up to that date will be totaled.", vbInformation , "Purpose"
    lastDate = InputBox("Enter the last date for the total."),"Last date"
    total = 0
    For Each ws In ActiveWorkbook
        With Worksheets("ws").Range("A3")
            Do Until .Offset(i, 0) = ""
                If .Offset(i, 0) <= lastDate Then total = total + .Offset(i, 2)
            Loop
        End With
    Next
    MsgBox "The total of all sales through " & Format(lastDate, "1/1/99") is " _
        & Format(Total,$#,##0), vbinformation, "Sales Total"
End Sub

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
    MsgBox "The total sales for " & selectedRep & " in " & state & was " _
        Format(Total,$#,##0), vbInformation
End Sub

Date SalesRep Amount 1/11/2011 Quick 232 1/11/2011 Tuesley 168 1/11/2011 Holder 177 1/11/2011 Richardson 102 1/11/2011 Wendel 123 1/11/2011 Riley 214 1/11/2011 Phillips 48 1/11/2011 McAfee 198 1/12/2011 Tuesley 111 1/12/2011 Sutanont 96 1/12/2011 Gerde 97 1/12/2011 Wood 92 1/13/2011 Dudley 108 1/14/2011 Ginter 64 1/14/2011 Ellison 83 1/14/2011 Kumar 105 1/14/2011 Sears 168 1/14/2011 Shewmaker 79 1/14/2011 Ebina 77 1/14/2011 Feldman 127 1/14/2011 Kartikahadi 94 1/14/2011 Tapper 103 1/14/2011 Liggett 78 1/14/2011 Bedrick 165 1/14/2011 Holder 84 1/14/2011 Yeiter 208 1/15/2011 Cheung 195 1/15/2011 Levitt 116 1/15/2011 Oman 81 1/15/2011 Koznicki 150 1/15/2011 Bang 115 1/15/2011 Wier 86 1/15/2011 Wood 131 1/15/2011 Vicars 175 1/16/2011 Anston 107 1/16/2011 Rodibaugh 106 1/16/2011 Nakamatsu 69 1/16/2011 Sahulcik 69 1/17/2011 Hayashiguchi 161 1/18/2011 Sutanont 206 1/18/2011 Mehay 59 1/18/2011 Tebawi 149 1/18/2011 Wendel 44 1/19/2011 Zakrzacki 144 1/19/2011 Suriany 57 1/20/2011 Hall 108 1/20/2011 Tebawi 153 1/20/2011 Lee 109 1/20/2011 Dewi 101 1/20/2011 Brulez 83 1/20/2011 Tan 144 1/20/2011 Folloder 132 1/20/2011 Bedrick 35 1/20/2011 Tracy 158 1/21/2011 Dewi 218 1/21/2011 Henderson 39 1/21/2011 Ziegler 235 1/21/2011 Ziegler 67 1/21/2011 Shah 118 1/21/2011 Lu 121 1/21/2011 Anston 227 1/21/2011 Le 241 1/21/2011 Rheem 62 1/21/2011 Yablonka 136 1/22/2011 Neville 75 1/22/2011 Wise 181 1/22/2011 Prigozen 56 1/22/2011 Sears 163 1/22/2011 Fox 196 1/22/2011 Arnett 73 1/22/2011 Yablonka 96 1/22/2011 Tapper 66 1/22/2011 Fant 176 1/22/2011 Dewi 154 1/22/2011 Myers 157 1/22/2011 Ziegler 150 1/22/2011 Lewis 172 1/22/2011 Nakamatsu 113 1/22/2011 Malinowski 91 1/22/2011 Sandberg 113 1/22/2011 Steffen 49 1/23/2011 Chen 131 1/23/2011 Lunsford 84 1/23/2011 Maloney 137 1/23/2011 Ripperger 152 1/24/2011 Ubelhor 164 1/24/2011 Staadeker 130 1/24/2011 Johnson 47 1/24/2011 Prigozen 138 1/24/2011 Bang 70 1/24/2011 Kline 62 1/24/2011 Shields 49 1/25/2011 Villard 194 1/26/2011 Rodibaugh 75 1/26/2011 Heintz 47 1/26/2011 Macey 204 1/26/2011 Majcher 62 1/27/2011 Liggett 91 1/27/2011 Mushlin 277 1/27/2011 Graves 269 1/27/2011 Dove 170 1/27/2011 Graves 116 1/27/2011 Tracy 31 1/27/2011 Cohn 108 1/27/2011 Chou 154 1/27/2011 Koznicki 241 1/28/2011 Lu 137 1/28/2011 Mehay 231 1/29/2011 Joy 150 1/29/2011 Theodas 169 1/29/2011 Dudley 77 1/29/2011 Anston 35 1/29/2011 Ripperger 245 1/29/2011 Hsu 197 1/29/2011 Kelker 71 1/29/2011 Steffen 63 1/29/2011 Holder 133 1/29/2011 Lane 274 1/29/2011 Riley 113 1/30/2011 Usman 134 1/30/2011 Hanley 120 1/30/2011 Mizrahi 167 1/30/2011 Hayes 182 1/30/2011 Brulez 54 1/30/2011 Myers 117 1/30/2011 Anston 122 1/31/2011 Zhou 159 2/1/2011 Maloney 213 2/1/2011 Eberhart 181 2/2/2011 Macey 95 2/2/2011 Folloder 149 2/3/2011 Hayashiguchi 39 2/3/2011 Stuckey 103 2/3/2011 Hayes 177 2/3/2011 McAfee 151 2/3/2011 Koznicki 112 2/4/2011 Shahrani 53 2/5/2011 Ripperger 78 2/5/2011 Dove 79 2/5/2011 Mushlin 75 2/5/2011 Barnett 89 2/5/2011 Tuesley 44 2/5/2011 Suriany 215 2/6/2011 Barnett 45 2/6/2011 Johnson 141 2/6/2011 Phillips 205 2/6/2011 Cadigan 200 2/6/2011 Sutanont 66 2/7/2011 Anston 76 2/8/2011 Levitt 84 2/8/2011 Johnson 61 2/8/2011 Wendel 139 2/9/2011 Yablonka 217 2/9/2011 Nakamatsu 173 2/9/2011 Zakrzacki 110 2/9/2011 Chandler 58 2/9/2011 Fox 64 2/9/2011 Barlog 127 2/9/2011 Pelger 56 2/9/2011 Suriany 69 2/9/2011 Sutanont 110 2/10/2011 Staadeker 147 2/10/2011 Ubelhor 49 2/10/2011 Hoffman 87 2/10/2011 Lewis 51 2/10/2011 Okrzesik 184 2/10/2011 Ehlers 90 2/10/2011 Le 172 2/10/2011 Kumar 166 2/10/2011 Samboen 82 2/10/2011 Steffen 222 2/11/2011 Tebawi 78 2/11/2011 Riley 257 2/11/2011 Ebina 50 2/11/2011 Lewis 129 2/12/2011 Henderson 51 2/12/2011 Bang 82 2/12/2011 Pruden 66 2/13/2011 Rodibaugh 121 2/13/2011 Cochran 111 2/13/2011 Prigozen 214 2/13/2011 Shah 88 2/14/2011 Arnett 180 2/14/2011 Wyatt 144 2/14/2011 Papp 130 2/15/2011 Treadway 180 2/16/2011 Jones 59 2/17/2011 Jones 101 2/17/2011 Ubelhor 186 2/17/2011 Brulez 132 2/17/2011 Hanley 171 2/17/2011 Tan 72 2/17/2011 Flotken 58 2/17/2011 Suriany 153 2/17/2011 Graves 161 2/18/2011 Papp 91 2/18/2011 Cohn 164 2/18/2011 Parakh 192 2/18/2011 Tapper 260 2/18/2011 Samboen 178 2/18/2011 Chen 100 2/19/2011 Shahrani 130 2/19/2011 Fant 132 2/19/2011 Tuesley 126 2/20/2011 Cheung 145 2/20/2011 Fant 55 2/20/2011 Darbro 73 2/20/2011 Hsu 94

Explanation / Answer

Sub ListStates()
    Dim ws As String, message As String
    message = "Here is a list of states:"
    For Each ws In ActiveWorkbook
        message = message & vbCrLf & "ws.Name"
    Next
    MsgBox message, vbInformation, "State list"
End Sub

Sub StateSearch()
    InputBox "Enter a state you'd like to search for.", "State search"
    FindState state, isFound
    MsgBox "State was " & isFound, vbInformation
End Sub

Sub FindState(state As String)
    Dim isFound As Boolean
    For Each ws In ActiveWorkbook
        If ws.Name = state Then
            isFound = True
            Exit For
        Else
            isFound = False
        End If
    Next
End Sub

Sub CountSales()
    Dim state As String, salesRep As String
    state = InputBox "Enter a state"
    salesRep = InputBox "Enter a SalesRep"
    On Error GoTo NoSuchState
    With Worksheets(state).Range("B3")
        Count = 0
        For Each cell In Range(.Offset(1, 0), .End)
            If cell = StateRep Then Count = Count + 1
        Next
    End With
    MsgBox "SalesRep made " Count " sales in " state, vbInformation
NoSuchState:
    MsgBox "The state " state " is not one of the worksheets.", vbInformation
End Sub

Sub TotalSales1()
    Dim lastDate As Date, total As Currency
    MsgBox "You'll now be asked for a date. Then the total of all " _
        " sales up to that date will be totaled.", vbInformation , "Purpose"
    lastDate = InputBox("Enter the last date for the total."),"Last date"
    total = 0
    For Each ws In ActiveWorkbook
        With Worksheets("ws").Range("A3")
            Do Until .Offset(i, 0) = ""
                If .Offset(i, 0) <= lastDate Then total = total + .Offset(i, 2)
            Loop
        End With
    Next
    MsgBox "The total of all sales through " & Format(lastDate, "1/1/99") is " _
        & Format(Total,$#,##0), vbinformation, "Sales Total"
End Sub

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
    MsgBox "The total sales for " & selectedRep & " in " & state & was " _
        Format(Total,$#,##0), vbInformation
End Sub

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

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