CODEWITHSUNDEEP
excelvba
Lee Read
Lee Read

Reputation: 39

Elegant way of shortening this code using a loops

In need of some help. I am copying data from a table to specific places in another and transposing it. I know there is a faster way of doing this with a loop but for the life of me i cant figure it out.

If you look at the code the only thing changing is the column number of "Daily Dotcom" (3,4,5 then 7,8,9 then 11,12,13 etc) and the Row number of "dotFigures" (2,3,4 then 7,8,9 then 12,13,14 etc)

After thats been done, I need to do exactly the same but with 2 other tables called "dailySeasonal" and "dailyManual" using arrays called "seaFigures" and "manFigures"

So you can see why im so desperate to shorten the code, as it will get ridiculously long winded.

For i = 1 To ActiveSheet.ListObjects("dailyDotcom").ListRows.Count

    If ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i, 1).Value = wkComm Then

        'planned FTAM
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i, 3).Value = dotFigures(2, 2)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 1, 3).Value = dotFigures(2, 3)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 2, 3).Value = dotFigures(2, 4)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 3, 3).Value = dotFigures(2, 5)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 4, 3).Value = dotFigures(2, 6)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 5, 3).Value = dotFigures(2, 7)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 6, 3).Value = dotFigures(2, 8)
        'attended FTAM
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i, 4).Value = dotFigures(3, 2)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 1, 4).Value = dotFigures(3, 3)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 2, 4).Value = dotFigures(3, 4)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 3, 4).Value = dotFigures(3, 5)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 4, 4).Value = dotFigures(3, 6)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 5, 4).Value = dotFigures(3, 7)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 6, 4).Value = dotFigures(3, 8)
        'absence FTAM
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i, 5).Value = dotFigures(4, 2)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 1, 5).Value = dotFigures(4, 3)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 2, 5).Value = dotFigures(4, 4)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 3, 5).Value = dotFigures(4, 5)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 4, 5).Value = dotFigures(4, 6)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 5, 5).Value = dotFigures(4, 7)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 6, 5).Value = dotFigures(4, 8)

        'planned FTPM
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i, 7).Value = dotFigures(7, 2)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 1, 7).Value = dotFigures(7, 3)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 2, 7).Value = dotFigures(7, 4)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 3, 7).Value = dotFigures(7, 5)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 4, 7).Value = dotFigures(7, 6)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 5, 7).Value = dotFigures(7, 7)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 6, 7).Value = dotFigures(7, 8)
        'attended FTPM
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i, 8).Value = dotFigures(8, 2)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 1, 8).Value = dotFigures(8, 3)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 2, 8).Value = dotFigures(8, 4)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 3, 8).Value = dotFigures(8, 5)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 4, 8).Value = dotFigures(8, 6)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 5, 8).Value = dotFigures(8, 7)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 6, 8).Value = dotFigures(8, 8)
        'absence FTPM
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i, 9).Value = dotFigures(9, 2)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 1, 9).Value = dotFigures(9, 3)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 2, 9).Value = dotFigures(9, 4)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 3, 9).Value = dotFigures(9, 5)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 4, 9).Value = dotFigures(9, 6)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 5, 9).Value = dotFigures(9, 7)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 6, 9).Value = dotFigures(9, 8)

        'planned Nights
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i, 11).Value = dotFigures(12, 2)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 1, 11).Value = dotFigures(12, 3)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 2, 11).Value = dotFigures(12, 4)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 3, 11).Value = dotFigures(12, 5)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 4, 11).Value = dotFigures(12, 6)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 5, 11).Value = dotFigures(12, 7)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 6, 11).Value = dotFigures(12, 8)
        'attended Nights
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i, 12).Value = dotFigures(13, 2)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 1, 12).Value = dotFigures(13, 3)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 2, 12).Value = dotFigures(13, 4)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 3, 12).Value = dotFigures(13, 5)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 4, 12).Value = dotFigures(13, 6)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 5, 12).Value = dotFigures(13, 7)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 6, 12).Value = dotFigures(13, 8)
        'absence Nights
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i, 13).Value = dotFigures(14, 2)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 1, 13).Value = dotFigures(14, 3)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 2, 13).Value = dotFigures(14, 4)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 3, 13).Value = dotFigures(14, 5)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 4, 13).Value = dotFigures(14, 6)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 5, 13).Value = dotFigures(14, 7)
        ActiveSheet.ListObjects("dailyDotcom").DataBodyRange(i + 6, 13).Value = dotFigures(14, 8)



    End If
Next i

Any help would be much appreciated!

Upvotes: 0

Views: 51

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

  1. Use a With statement to shorten these long lines.
  2. Use Option Explicit and declare all your variables.
  3. Use a second loop j to count the other steps from 0 to 6

So you end up with:

Option Explicit

Public Sub DoMyStuff()
    With ActiveSheet.ListObjects("dailyDotcom")
        Dim i As Long, j As Long

        For i = 1 To .ListRows.Count
            If .DataBodyRange(i, 1).Value = wkComm Then
                For j = 0 To 6
                    .DataBodyRange(i + j, 3).Value = dotFigures(2, 2 + j) 'planned FTAM
                    .DataBodyRange(i + j, 4).Value = dotFigures(3, 2 + j) 'attended FTAM
                    .DataBodyRange(i + j, 5).Value = dotFigures(4, 2 + j) 'absence FTAM

                    .DataBodyRange(i + j, 7).Value = dotFigures(7, 2 + j) 'planned FTPM
                    .DataBodyRange(i + j, 8).Value = dotFigures(8, 2 + j) 'attended FTPM
                    .DataBodyRange(i + j, 9).Value = dotFigures(9, 2 + j) 'absence FTPM

                    .DataBodyRange(i + j, 11).Value = dotFigures(12, 2 + j) 'planned Nights
                    .DataBodyRange(i + j, 12).Value = dotFigures(13, 2 + j) 'attended Nights
                    .DataBodyRange(i + j, 13).Value = dotFigures(14, 2 + j) 'absence Nights
                Next j
            End If
        Next i
    End With
End Sub

Upvotes: 1

Related Questions