CODEWITHSUNDEEP
excelvba
ahmed519
ahmed519

Reputation: 11

Skip blanks while running "For loop"

Hello i write simple code to compare dates in range: "starting from cell "G9" till end of column" compare dates in this range with today's date and return the difference in days to be placed the corresponding cell in column "P"

The problem is: i need to skip blanks while looping so if the cell is blank vba will skip and go to next cell 

Sub overduedate()
    Dim LastRow  As Long, i As Long

    With Worksheets("sheet1")
        LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
        For i = 9 To LastRow
            .Range("P" & i).Value = DateDiff("d", .Range("G" & i).Value, Date)
        Next i
    End With
End Sub

Upvotes: 1

Views: 124

Answers (1)

Zack E
Zack E

Reputation: 706

Like this:

Sub overduedate()
    Dim LastRow  As Long, i As Long
    Dim cell as range

    With Worksheets("sheet1")
        LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
        For i = 9 To LastRow
            If cell.value <> vbNullString then
                .Range("P" & i).Value = DateDiff("d", .Range("G" & i).Value, Date)
            End If
        Next i
    End With
End Sub

Or you can use the If Statement without the End If like below:

Sub overduedate()
    Dim LastRow  As Long, i As Long
    Dim cell as range

    With Worksheets("sheet1")
        LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
        For i = 9 To LastRow
            If cell.Value <> vbNullString then .Range("P" & i).Value = DateDiff("d", .Range("G" & i).Value, Date)
        Next i
    End With
End Sub

Upvotes: 2

Related Questions