Multiple Row Formula

[VbaHell]

Hello all in Mr Excel land

This maybe a stupid question for people in the know but I need help on this please

The formula below is what I need

[With Range("B4:B"& LastRow)
.FormulaR1C1 = "=SUMIF(Suppliers!C1,RC1,Suppliers!C2)"
.Value = .Value
End With]

But I want to include this formula every fourth column

IE: "F,J,N,R" and so on until column "AG"

Is there a simple why to complete this please

 

[Fluff]

something like

For i= 2 to 33 step 4
With Range(cells(4,i),cells(lastrow,i))
        .FormulaR1C1 = "=SUMIF(Suppliers!C1,RC1,Suppliers!C2)"
        .Value = .Value
    End With
Next i

 

[Joe4]

Try:

    Dim myCol As Long
    For myCol = 5 To 33 Step 4
        With Range(Cells(4, myCol), Cells(lastRow, myCol))
            .FormulaR1C1 = "=SUMIF(Suppliers!C1,RC1,Suppliers!C2)"
            .Value = .Value
        End With
    Next myCol

 

[VbaHell]

Thank you very much Fluff

Works a treat

 

[Fluff]

Glad we could help & thanks for the feedback

 

[VbaHell]

Thanks Joe4

This also works, always interesting to see the slightly different approaches to solve the same issue

 

[wideboydixon]

If I understand this correctly, it's performing a SUMIF on the Suppliers sheet to match up on Column A with the summed values in Column B. The conditional value is taken from column A on the current sheet. This means that all values in columns F, J, N, R ... etc. will be exactly the same. Also, AG is column 33 so shouldn't have the formula/value inserted. In that case you can possibly make this quicker by not re-performing the calculation each time:

Dim ThisCol As Long
Dim StoredValues As Variant

With Range("B4:B" & LastRow)
    .FormulaR1C1 = "=SUMIF(Suppliers!C1,RC1,Suppliers!C2)"
    StoredValues = .Value
End With

For ThisCol = 2 To 34 Step 4 ' Decide on 34 or 30
    Range(Cells(4, ThisCol), Cells(LastRow, ThisCol)).Value = StoredValues
Next ThisCol

WBD