VBA: Loop Through a Range
If you want to test a condition for each cell in a range using VBA, the best way is to loop through the range, testing each cell. Here are two code examples to demostrate how to loop through a range. You can replace the range value for whatever range you need to loop through.
The first example loops through the entire column A range. The if statement tests the condition if the cell contains the text “FindMe” and shows a message box with the location of the text if found.
Public Sub LoopColumn()
Dim c As Range
For Each c In Range("A:A")
If c.Value = "FindMe" Then
MsgBox "FindMe found at " & c.Address
End If
Next c
The next example loops through row 1 considering the entire row a range.
Public Sub LoopRow()
Dim c As Range
For Each c In Range("1:1")
If c.Value = "FindMe" Then
MsgBox "FindMe found at " & c.Address
End If
Next c
End Sub
dear sir,
in the first example, you forget to put the End Sub at the end of the program.
I learn something from this program. Thanks
Hi,
How to manually increment while using FOR EACH?
e.g.
for each cell in range(abc)
if cell.value = “YES” then
–>{increment cell = cell + 5}
end if
next
we do it in normal FOR loops
like.. if {something} then i = i+5
I would like to achieve the same while using the FOR EACH loop
thanks in advance!
Rishi
Please give me the code to populate the recordset data into the excel file,where record set is having the data of 80,000 rows and having 500 columns.
Please help me.
Thanks