Excel, how to add IF into a loop-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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
    Next
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.