Excel, how to add IF into a loop

I have a IF-Statement, and I need to loop it throug column F.

This loop checks for the word "empty" in column F and if found, it gets entered into columns G too. In column H the current date gets added, if it was not already in it. If F and G have "empty" in it, and H a date, the If-Statement gets ended.

If Range("F2").Value = "empty" And Range("G2").Value = "" Then
Range("G2").Value = "empty"
ElseIf (Range("F2").Value = "empty" And Range("G2").Value = "empty") And Range("H2").Value = "" Then
Range("H2") = Date
ElseIf (Range("F2").Value = "empty" And Range("G2").Value = "empty") And Range("H2").Value <> "" Then
End If

Can someone help me to add this into a loop, that goes trough the lines?

It manly needs to go trough line 2 to 1500.

Any help would be apprechiated.

Kind regards.

3 answers

  • answered 2022-05-04 10:36 Sphinx

    Try something like this

        Dim i as long
        For i = 2 to 1500 
            If Range("F" & i).Value = "empty" And Range("G" & i).Value = "" Then
               Range("G" & i).Value = "empty"
            ElseIf (Range("F" & i).Value = "empty" And Range("G" & i).Value = "empty") And Range("H" & i).Value = "" Then
               Range("H" & i) = Date
            ElseIf (Range("F" & i).Value = "empty" And Range("G" & i).Value = "empty") And Range("H" & i).Value <> "" Then
               'do something 
            End If
        Next i 

  • answered 2022-05-04 10:43 VBasic2008

    Nested Statements in a Loop

    Sub NestedStatements()
        Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
        Dim rg As Range: Set rg = ws.Range("F2:H1500")
        Dim rrg As Range
        For Each rrg In rg.Rows
            If CStr(rrg.Cells(1).Value) = "empty" Then
                Select Case CStr(rrg.Cells(2).Value)
                Case ""
                    rrg.Cells(2).Value = "empty"
                Case "empty"
                    If CStr(rrg.Cells(3).Value) = "" Then
                        rrg.Cells(3).Value = Date
                    End If
                End Select
            End If
        Next rrg
    End Sub

  • answered 2022-05-04 10:44 Ike

    I would create a single sub to do the job - to which you pass the range that should be checked:

    Option Explicit
    Private Const colF As Long = 6
    Private Const colG As Long = 7
    Private Const colH As Long = 8
    '-->> this is an example of how to call the sub
    Sub test_checkColumnsFtoH()
    checkColumnsFtoH ThisWorkbook.Worksheets("Table1").Range("A1:I500")
    End Sub
    '-->> this is your new sub
    Sub checkColumnsFtoH(rgToBeChecked As Range)
    Dim i As Long
    With rgToBeChecked
        For i = 2 To .Rows.Count
            If .Cells(i, colF).Value = "empty" And .Cells(i, colG).Value = "" Then
                .Cells(i, colG).Value = "empty"
            ElseIf (.Cells(i, colF).Value = "empty" And .Cells(i, colG).Value = "empty") _
                    And .Cells(i, colH).Value = "" Then
                .Cells(i, colH) = Date
            End If
    End With
    End Sub
    • I am using the cells property to avoid string concatination ("H" & i)
    • you don't need the last elseif - as nothing happens there.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum