Confused with nested Loops VBA Again

Question

Once again I've got myself into trouble with something that should be rather basic. I know there is a faster solution to this problem, however I cant seem to get the loops right.

Essentially I would like to shorten this code, as I have a larger table and array to deal with next and my current solution below would require numerous lines.

The source table has 10 columns and 4 rows.. I need to read data from the source table and assign it to one line of the destination table "d105WeeklyTable"

Any help would be greatly appreciated.

With ActiveSheet.ListObjects("d105WeeklyTable")
    For i = 1 To .ListRows.Count
        If .DataBodyRange(i, 2).Value = wkComm Then

                .DataBodyRange(i, 132).Value = d105TicksheetNightsFigures(1, 2)
                .DataBodyRange(i, 133).Value = d105TicksheetNightsFigures(1, 3)
                .DataBodyRange(i, 134).Value = d105TicksheetNightsFigures(1, 4)
                .DataBodyRange(i, 135).Value = d105TicksheetNightsFigures(1, 5)
                .DataBodyRange(i, 136).Value = d105TicksheetNightsFigures(1, 6)
                .DataBodyRange(i, 137).Value = d105TicksheetNightsFigures(1, 7)
                .DataBodyRange(i, 138).Value = d105TicksheetNightsFigures(1, 8)
                .DataBodyRange(i, 139).Value = d105TicksheetNightsFigures(1, 9)
                .DataBodyRange(i, 140).Value = d105TicksheetNightsFigures(1, 10)

                .DataBodyRange(i, 141).Value = d105TicksheetNightsFigures(2, 2)
                .DataBodyRange(i, 142).Value = d105TicksheetNightsFigures(2, 3)
                .DataBodyRange(i, 143).Value = d105TicksheetNightsFigures(2, 4)
                .DataBodyRange(i, 144).Value = d105TicksheetNightsFigures(2, 5)
                .DataBodyRange(i, 145).Value = d105TicksheetNightsFigures(2, 6)
                .DataBodyRange(i, 146).Value = d105TicksheetNightsFigures(2, 7)
                .DataBodyRange(i, 147).Value = d105TicksheetNightsFigures(2, 8)
                .DataBodyRange(i, 148).Value = d105TicksheetNightsFigures(2, 9)
                .DataBodyRange(i, 149).Value = d105TicksheetNightsFigures(2, 10)

                .DataBodyRange(i, 150).Value = d105TicksheetNightsFigures(3, 2)
                .DataBodyRange(i, 151).Value = d105TicksheetNightsFigures(3, 3)
                .DataBodyRange(i, 152).Value = d105TicksheetNightsFigures(3, 4)
                .DataBodyRange(i, 153).Value = d105TicksheetNightsFigures(3, 5)
                .DataBodyRange(i, 154).Value = d105TicksheetNightsFigures(3, 6)
                .DataBodyRange(i, 155).Value = d105TicksheetNightsFigures(3, 7)
                .DataBodyRange(i, 156).Value = d105TicksheetNightsFigures(3, 8)
                .DataBodyRange(i, 157).Value = d105TicksheetNightsFigures(3, 9)
                .DataBodyRange(i, 158).Value = d105TicksheetNightsFigures(3, 10)

                .DataBodyRange(i, 159).Value = d105TicksheetNightsFigures(4, 2)
                .DataBodyRange(i, 160).Value = d105TicksheetNightsFigures(4, 3)
                .DataBodyRange(i, 161).Value = d105TicksheetNightsFigures(4, 4)
                .DataBodyRange(i, 162).Value = d105TicksheetNightsFigures(4, 5)
                .DataBodyRange(i, 163).Value = d105TicksheetNightsFigures(4, 6)
                .DataBodyRange(i, 164).Value = d105TicksheetNightsFigures(4, 7)
                .DataBodyRange(i, 165).Value = d105TicksheetNightsFigures(4, 8)
                .DataBodyRange(i, 166).Value = d105TicksheetNightsFigures(4, 9)
                .DataBodyRange(i, 167).Value = d105TicksheetNightsFigures(4, 10)

        End If

    Next i
End With

Its an 2 dimensional array that ive passed data from a Table in an external workbook. — Lee Read
– Lee Read, Commented May 30, 2018 at 11:59

Ryszard Jędraszyk · Accepted Answer · 2018-05-30 12:04:40Z

2

Try this code (transfers data starting from 2nd column):

Const tRows as Long = 4
Const tColumns as Long = 10    

Dim i as Long, rowCounter as Long, colCounter as Long, outStart as Long
outStart = 132

With ActiveSheet.ListObjects("d105WeeklyTable")
    For i = 1 To .ListRows.Count
        If .DataBodyRange(i, 2).Value = wkComm Then
            For rowCounter = 1 to tRows
                For colCounter = 2 to tColumns                        
                    .DataBodyRange(i, outStart).Value = d105TicksheetNightsFigures(rowCounter, colCounter)
                    outStart = outStart + 1                        
                Next colCounter
            Next rowCounter
        End If
    Next i
End With

edited May 30, 2018 at 12:04

answered May 30, 2018 at 12:01

Ryszard Jędraszyk

2,4414 gold badges26 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lee Read Over a year ago

Thanks I will try, however i dont need the 1st column in the table hence why only 9 columns are in the code. I will see if i can adjust the code to only read columns 2-10 then drop to next row

Ryszard Jędraszyk Over a year ago

I 've adjusted it.

Lee Read Over a year ago

Thanks ill give it a go, and let you know. Really appreaciate the quick reply

Lee Read Over a year ago

Great! it works like a charm and solves the next problem I was going to have. I'll try and understand it and hopefully I'll stop running into these same kind of problems in the future.

Collectives™ on Stack Overflow

Confused with nested Loops VBA Again

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related