CODEWITHSUNDEEP
vbaexcel
TonyP
TonyP

Reputation: 333

Inserting a formula into an array based on variables and offset function

I am trying to get my code to insert a formula into an array based on how many data points there are in my data set. The code below almost works but after the first iteration of X is complete it does not insert the formula into the all the rows in the columns.

Worksheets(" Branded").Range("C3").Formula = "=COUNTIFS(" & r.Address(ReferenceStyle:=xlR1C1) & ",RC2, " & r2.Address(ReferenceStyle:=xlR1C1) & ",R2C)"

For Y = 1 To Column_Limit1 - 1
    Range("C3").Offset(0, Y).Formula = "=COUNTIFS(" & r.Address(ReferenceStyle:=xlR1C1) & ",RC2, " & r2.Address(ReferenceStyle:=xlR1C1) & ",R2C)"
    For X = 1 To Row_Limit1 - 1
        Range("C3").Offset(X, 0).Formula = "=COUNTIFS(" & r.Address(ReferenceStyle:=xlR1C1) & ",RC2, " & r2.Address(ReferenceStyle:=xlR1C1) & ",R2C)"
    Next X
Next Y

Upvotes: 0

Views: 79

Answers (1)

Scott Craner
Scott Craner

Reputation: 152495

Change to this:

With Worksheets(" Branded")
    .Range(.Cells(3, 3), .Cells(Row_Limit1 + 2, Column_Limit1 + 2)).FormulaR1C1 = "=COUNTIFS(" & r.Address(ReferenceStyle:=xlR1C1) & ",RC2, " & r2.Address(ReferenceStyle:=xlR1C1) & ",R2C)"
    .Range(.Cells(Row_Limit1 + 3, 3), .Cells(Row_Limit1 + 3, Column_Limit1 + 2)).FormulaR1C1 = "=SUM(R3C:R[-1]C)"
End With

When using R1C1 there is no need of a loop.

Upvotes: 3

Related Questions