Results 1 to 6 of 6

Using For Each to iterate each cell in a row

Threaded View

  1. #1
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,968

    Using For Each to iterate each cell in a row

    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>?
    Attached Files Attached Files
    Last edited by 6StringJazzer; 10-02-2011 at 12:12 PM.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1