I have a Sub that takes a range as a parameter, then iterates through the entire range. However, when the parameter is a row, the loop does not iterate through each cell in the row. Instead it treats the row as atomic and the iteration variable takes on the whole row.
Although my original project is more complicated, I have boiled the problem down to its essence. Here is the code:
Option Explicit
Private Sub buttonRun_Click()
IterateRange UsedRange, "Iteration through entire used range"
IterateRange [A1].EntireRow, "Iteration through a row"
End Sub
Public Sub IterateRange(r As Range, desc As String)
Dim c As Range
Dim s As String
For Each c In r
s = s & c.Address & "->" & c.Value & ";"
Next c
MsgBox desc & vbCrLf & s
End Sub
In this example, when the calling Sub passes UsedRange, everything works fine (see attachment). But when it passes EntireRow, there is a runtime error on the assignment statement because c has the address $1:$1, the entire row.
How can I construct a loop that will work correctly when this Sub is passed a row? Do I have to iterate through rows and columns explicitly rather than using For Each <range>?
Bookmarks