CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

r1c1 formula not working

I am trying to rank values using column J and tie breaker in column K with the result populating in column N. Column J & K are values.

Somehow it only generates one value wherever my cell is pointed at, which means if I run the vba codes at cell C19, it will just populate value 1 in C19, not from N6 where I want the results to be.

Here are my codes,

Sub test()
Dim LR1 As Long
LR1 = Range("J" & Rows.Count).End(xlUp).Row
With Range("N6:N" & LR1)
   ActiveCell.FormulaR1C1 = "=1+SUMPRODUCT(--(R6C10:R33C10<RC[-4]))+SUMPRODUCT(--(R6C11:R33C11<RC[-3]),--(R6C10:R33C10=RC[-4]))"
End With
End Sub

I am not sure what went wrong. I tried to do it manually using the excel formula and its working fine but not my vba codes.

Upvotes: 0

Views: 1131

Answers (1)

IvenBach
IvenBach

Reputation: 583

ActiveCell is your issue.

Change ActiveCell.FormulaR1C1 = "=1+SUMPRODUCT(--(R6C10:R33C10<RC[-4]))+SUMPRODUCT(--(R6C11:R33C11<RC[-3]),--(R6C10:R33C10=RC[-4]))"

To .FormulaR1C1 = "=1+SUMPRODUCT(--(R6C10:R33C10<RC[-4]))+SUMPRODUCT(--(R6C11:R33C11<RC[-3]),--(R6C10:R33C10=RC[-4]))"

Remove that and it should do what you want.

You will want to fully qualify your Range references that way they aren't depending on the ActiveSheet. This will provide you with consistent behavior and results.

Upvotes: 1

Related Questions