Loop without do error, excel vba

Question

I'm trying to read a macro that will sum up corresponding values associated with a series of dates. IE- Jan. 1st will have 20 rows, with corresponding entries of 20, 50, 80, in respective columns. I want a macro that will sum all of these entries, and once all of the Jan. 1st entries are summed, then it will move on to Jan. 2nd, and sum all of those entries. The code that I have written so far gives me a loop without do error.

Private Sub CommandButton1_Click()

Dim dateCheck As String
Dim shipDay As Date
Dim L As Integer
Dim i As Integer
Dim S As Integer
Dim search As String
Dim usaTotal As Long
Dim usaCredit As Long
Dim usaDebit As Long


L = 10
i = 3


dateCheck = InputBox("What Date is Ship Day 1?", "Ship Day Entry")

If IsDate(dateCheck) Then shipDay = DateValue(dateCheck) _
Else: MsgBox ("Invalid Date") ' Prompts user for ship day 1, and checks if actual date




While Not Worksheets("Sheet1").Cells(i, 8).Value = "" ' Runs until all cell rows are accounted for

For S = 0 To 29

shipDay = shipDay + S

Do Until shipDay <> Worksheets("Sheet1").Cells(i, 8).Value ' Execute until new date

search = Worksheets("sheet1").Cells(i, 12).Value ' Variable to use InStr to check for "CAN"

If (((shipDay = Worksheets("Sheet1").Cells(i, 8).Value) And _
InStr(1, search, "CAN", vbBinaryCompare) = 0) _
And (Worksheets("Sheet1").Cells(i, 6).Text = "Invoice")) Then

'Check that date matches, and that it isn't Canada, and that order is an invoice

usaDebit = Worksheets("Sheet1").Cells(i, 22).Value ' Account for Debits
usaCredit = Worksheets("Sheet1").Cells(i, 24).Value ' Account for Credits

usaTotal = usaTotal + usaCredit - usaDebit  ' Calculate contribution

i = i + 1

End If

Loop

MsgBox (usaTotal)


Next S

Worksheets("JUNE Canada").Cells(i, 22).Value = usaTotal

MsgBox (usaTotal)

 ' Need code here that will input final usaTotal into respective space

MsgBox (usaTotal)

Wend ' End of Initial "while not"

End Sub

Your first 'If' statement has no 'End If'

Sam
– Sam

2015-07-02 15:15:15 +00:00
Commented Jul 2, 2015 at 15:15 — Sam
– Sam, Commented Jul 2, 2015 at 15:15
Indentation is your friend.

vacip
– vacip

2015-07-02 15:26:41 +00:00
Commented Jul 2, 2015 at 15:26 — vacip
– vacip, Commented Jul 2, 2015 at 15:26

sous2817 · Accepted Answer · 2015-07-02 15:16:56Z

2

Looks like you're missing an "end if" statement at the top. This seemed to work for me (I also added an "Exit Sub" if they inputted an invalid date. That just seemed to make sense, but you should take it out if an invalid date doesn't impact the rest of the code):

Private Sub CommandButton1_Click()

    Dim dateCheck As String
    Dim shipDay As Date
    Dim L As Integer
    Dim i As Integer
    Dim S As Integer
    Dim search As String
    Dim usaTotal As Long
    Dim usaCredit As Long
    Dim usaDebit As Long

    L = 10
    i = 3

    dateCheck = InputBox("What Date is Ship Day 1?", "Ship Day Entry")

    If IsDate(dateCheck) Then
        shipDay = DateValue(dateCheck)
    Else:
        MsgBox ("Invalid Date")
        Exit Sub
    End If ' Prompts user for ship day 1, and checks if actual date

    While Not Worksheets("Sheet1").Cells(i, 8).Value = "" ' Runs until all cell rows are accounted for

        For S = 0 To 29
            shipDay = shipDay + S
            Do Until shipDay <> Worksheets("Sheet1").Cells(i, 8).Value ' Execute until new date

                search = Worksheets("sheet1").Cells(i, 12).Value ' Variable to use InStr to check for "CAN"

                If (((shipDay = Worksheets("Sheet1").Cells(i, 8).Value) And _
                InStr(1, search, "CAN", vbBinaryCompare) = 0) _
                And (Worksheets("Sheet1").Cells(i, 6).Text = "Invoice")) Then

                'Check that date matches, and that it isn't Canada, and that order is an invoice

                usaDebit = Worksheets("Sheet1").Cells(i, 22).Value ' Account for Debits
                usaCredit = Worksheets("Sheet1").Cells(i, 24).Value ' Account for Credits

                usaTotal = usaTotal + usaCredit - usaDebit  ' Calculate contribution

                i = i + 1

                End If

            Loop

            MsgBox (usaTotal)
        Next S

        Worksheets("JUNE Canada").Cells(i, 22).Value = usaTotal
        MsgBox (usaTotal)

         ' Need code here that will input final usaTotal into respective space

        MsgBox (usaTotal)

    Wend ' End of Initial "while not"
End Sub

answered Jul 2, 2015 at 15:16

sous2817

3,9602 gold badges37 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Max H Over a year ago

Thank you, that solves that problem. I'll give you the check mark when it allows me. However, when I run this code... My excel stops responding and I need to force end task. Any idea why that is?

sous2817 Over a year ago

Without seeing your workbook, my guess is that either your While or your Do Until loops is not hitting the termination criteria. Best bet is to set a break point at both conditions and step through the code evaluating the criteria to make sure it is what you think it should be.

Collectives™ on Stack Overflow

Loop without do error, excel vba

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related