CODEWITHSUNDEEP
excelvba
shweta agnihotri
shweta agnihotri

Reputation: 3

Macros to copy formula from 1 row to another skipping 2-3 rows

I have values in column E6, F6 to P6 from which I have quarterly average formula in row R6 to U6. In row 7 and 8 I have another data and again in row 9 I have data which I need to use average formula. Likewise, I have data which need to average out from row 6,9,12,15, respectively.Hence, could anyone please help me to writing macros formula leaving 2 rows every time

below is the macros I wrote for 

Dim LastRow As Long
 LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("R6:R" & LastRow).Formula = "=if(E:E=0,0,Average(E6:G6))"
.Range("S6:S" & LastRow).Formula = "=if(G:G=0,0,Average(H6:J6))"
.Range("T6:T" & LastRow).Formula = "=if(J:J=0,0,Average(K6:M6))"
.Range("U6:U" & LastRow).Formula = "=if(M:M=0,0,Average(N6:P6))"
.Range("V6:V" & LastRow).Formula = "=if(R:R=0,0,Average(R6:U6))"
.Range("R6:V6" & LastRow).Copy
.Range("R6:V6" & LastRow).PasteSpecial xlValues
Application.CutCopyMode = False

Upvotes: 0

Views: 148

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

To count 6, 9, 12, 15 you can use a For loop with Step 3

Option Explicit

Public Sub Step3Example()    
    Dim i As Long
    For i = 6 To 15 Step 3
        Debug.Print i 'output: 6, 9, 12, 15
    Next i    
End Sub

And you can use i in your formula representing 6, 9, 12 and 15:

.Range("R6:R" & LastRow).Formula = "=if(E:E=0,0,Average(E" & i & ":G" & i & "))"

Upvotes: 2

Related Questions