Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    97

    Unanswered: Looping through cells by row in a discontinuous range

    Hi all,

    I have been unable to find a solution to a problem I am having.

    I have a simple table, see attached image.

    I have a discontinuous range (DataRun) that essentially selects three of the five columns in the table and is as follows =Sheet1!$A$1:$A$15,Sheet1!$C$1:$C$15,Sheet1!$E$1:$ E$15

    What I am looking to be able to achieve is to loop through the cells in this range by row using VBA i.e. the loop would run A1, C1, E1, A2, C2, E2, A3 and so on...

    I am aware that there is probably a simple solution but have been unable to find it as yet. I would be really grateful for any help or pointers.

    Many thanks

    Dave
    Attached Thumbnails Attached Thumbnails 001.JPG  
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Here is one solution to looping through a fix number of rows that include text values, which you have on row #1.
    With data in Sheet1, this code just copies each row from columns A, C & E, to Sheet2 columns A, B & C.
    Code:
    Sub Macro1()
    Dim MyRng As Range
    Dim qty As String, itemDesc As String, totalCost As String
    
        Set MyRng = Range("A1:E15")
        
        Sheets("Sheet2").Cells.ClearContents
        
        For Each r In MyRng.Rows
            qty = Cells(r.Row(), 1).Value
            itemDesc = Cells(r.Row(), 3).Value
            totalCost = Cells(r.Row(), 5).Value
            
            Sheets("Sheet2").Cells(r.Row(), 1).Value = qty
            Sheets("Sheet2").Cells(r.Row(), 2).Value = itemDesc
            Sheets("Sheet2").Cells(r.Row(), 3).Value = totalCost
        Next
        
        
    End Sub

  3. #3
    Join Date
    Jan 2012
    Posts
    97
    Thank you kindly, works perfectly.

    Much appreciated.

    Dave
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

Posting Permissions

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