Excel VBA looping issue

Question

I am writing a macro that starts with the entry of a valid date. After the date entry, I then want the macro to search a worksheet for all rows that have this date. Then, I want all of the respective credits and debits totaled for those rows, and then I want those totals placed into another worksheet. This is the code that I wrote for this.

The problem is that EVENTUALLY, the code will loop infinitely within "Search =, If, and End If." If I place an "Else: Next I" before the End If, then I get an error, Else without For prompt.

Any suggestions?

Private Sub CommandButton3_Click()

Dim dateCheck As String
Dim lastRow As Long
Dim L As Integer
Dim I As Long
Dim shipDay As Date
Dim search As String
Dim usaTotal As Long
Dim usaCredit As Long
Dim usaDebit As Long

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

For L = 0 To 30  ' Execute this code for 1 month worth of dates

    shipDay = shipDay + L

    MsgBox shipDay

    For I = 1 To 10000  ' Check every row of the worksheet

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


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

            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

            ' This is where I tried placing an Else: Next I, which gives me an else without for prompts.

        End If

    Next I

MsgBox (usaTotal)
Worksheets("JUNE canada").Cells(L + 10, 4).Value = usaTotal / 1000  'Enter value into sheet
usaTotal = 0    ' Reset usaTotal value

Next L

Answer 1

Use the used range instead of 10000

    Dim lROw as Long
    Do While lRow <= ws.UsedRange.Rows.Count

        'Do stuff here.

        lRow = lRow + 1
        ws.Range("A" & lRow).Activate
    Loop

So your code would look something like this

Private Sub CommandButton3_Click()

Dim dateCheck As String
Dim lastRow As Long
Dim L As Integer
Dim I As Long
Dim shipDay As Date
Dim search As String
Dim usaTotal As Long
Dim usaCredit As Long
Dim usaDebit As Long

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

For L = 0 To 30  ' Execute this code for 1 month worth of dates

shipDay = shipDay + L

MsgBox shipDay

    I = 1
    Do While I <= ws.UsedRange.Rows.Count
        search = Worksheets("sheet1").Cells(I, 12).Value  ' Variable to use InStr to check for "CAN"

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

            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

            ' This is where I tried placing an Else: Next I, which gives me an else without for prompts.
        elseif instr(something, something) then

        End If

        I = I + 1
        ws.Range("A" & I).Activate
    Loop

MsgBox (usaTotal)
Worksheets("JUNE canada").Cells(L + 10, 4).Value = usaTotal / 1000  'Enter value into sheet
usaTotal = 0    ' Reset usaTotal value

Next L