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 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.
How many English words
do you know?
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
how many words do you know
Powered by Examplum