Loop through a column, while checking if cell is empty

Question

I'm trying to loop through a column, while checking if cell in row is empty and if today's date minus date in table are higher or the same as 7. If requirements are met send email.

Private Sub Workbook_Open()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim datum1 As Date
Dim datum2 As Long
Dim danasnji1 As Date
Dim danasnji2 As Long
Dim x As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastrow
    If IsEmpty(Cells(x, 10).Value) Then
        x = x + 1
    Else
        datum1 = Cells(x, 10).Value
        datum2 = datum1
        danasnji1 = Date
        danasnji2 = danasnji1
    
        If danasnji2 - datum2 >= 7 Then
            Set myApp = New Outlook.Application
            Set mymail = myApp.CreateItem(olMailItem)
            mymail.To = "examlemail@mail"
            With mymail
                .Subject = "Test"
                .body = "Body Code"
                .Send
            End With
            Cells(x, 10).Font.Color = RGB(255, 0, 0)
        End If
    End If
Next
Set myApp = Nothing
Set mymail = Nothing
End Sub

Picture of Excel table column:

This code sends email but the loop skips some cells. There should be 5 emails, but it sends 3. Cells that worked are coloured red.

First remove x = x + 1 never change a for variable just leave the if part empty for the moment — Red Hare, Jun 12 '23 at 10:03
Thanks for the quick replay, this change worked, script is now working as intended. — Aleksa Djuric, Jun 12 '23 at 10:11

score 2 · Accepted Answer · answered Jun 12 '23 at 10:11

Never modify the counting variable (in your case x) inside the loop, it is incremented automatically by the For-statement.

In your case, you increment it additionally to the autoincrement, causing that the code skips a line when an "empty" line is found. Change your code to

For x = 2 To lastrow
    If Not IsEmpty(Cells(x, 10).Value) Then
        datum1 = Cells(x, 10).Value
        datum2 = datum1
        danasnji1 = Date
        danasnji2 = danasnji1
    
        If danasnji2 - datum2 >= 7 Then
            Set myApp = New Outlook.Application
            Set mymail = myApp.CreateItem(olMailItem)
            mymail.To = "examlemail@mail"
            With mymail
                .Subject = "Test"
                .body = "Body Code"
                .Send
            End With
            Cells(x, 10).Font.Color = RGB(255, 0, 0)
        End If
    End If
Next

Note that you should qualify your Cells usages: Tell VBA with which worksheet you want to work with. If you write IsEmpty(Cells(x, 10).Value), VBA will assume that you want to work with the active sheet, and that is not always what you want. Have a close look to How to avoid using Select in Excel VBA

Thanks for the quick replay, this change worked, script is now working as intended. — Aleksa Djuric, Jun 12 '23 at 10:16

Loop through a column, while checking if cell is empty

1 Answers1