VBA activecell.formulaR1C1 absolute with variable

Question

I'm currently studying on my own vba so thanks in advance for taking the time to read this. I've researched a lot and got stuck with a vba error regarding the variables of my formula, the code is the following:

Dim i As Long
Dim j As Long
Dim k As Integer
Dim iVal As Byte

With ActiveSheet
    iVal = .Cells(.Rows.Count, "A").End(xlUp).Row
End With


j = 2
k = -1

For i = 3 To iVal

j = j+1
k = k-1

Range("L3").Select
ActiveCell.FormulaR1C1 = "=IF(R" & i & "C5=R[" & k & "])"
'ActiveCell.FormulaR1C1 = "=+IF(R3C5=R[-2]C,R3C1,"" "")"
Selection.AutoFill Destination:=Range("L" & i & ": W" & i),      Type:=xlFillDefault

This is what I'm trying to do "automatic" with the For statement

Range("L3").Select
ActiveCell.FormulaR1C1 = "=+IF(R3C5=R[-2]C,R3C1,"" "")"
Selection.AutoFill Destination:=Range("L3:W3"), Type:=xlFillDefault

Range("L4").Select
ActiveCell.FormulaR1C1 = "=+IF(R4C5=R[-3]C,R4C1,"" "")"
Selection.AutoFill Destination:=Range("L4:W4"), Type:=xlFillDefault

Range("L5").Select
ActiveCell.FormulaR1C1 = "=+IF(R5C5=R[-4]C,R5C1,"" "")"
Selection.AutoFill Destination:=Range("L5:W5"), Type:=xlFillDefault
Next

I think I'm really close but must be a minor detail I'm missing, thanks.

Since you are learning, [see this regarding Select](http://stackoverflow.com/q/10714251/445425) — chris neilsen, Dec 09 '15 at 23:39

Scott Craner · Accepted Answer · 2015-12-09T23:02:05.257

2

Try this:

Range("L" & i & ":W" & i).FormulaR1c1 = "=IF(R" & i & "C5 = R[" & k & "]C,R" & i & "C1,"" "")"

With r1c1 there is no need of autofill.

edited Dec 09 '15 at 23:02

answered Dec 09 '15 at 22:56

Scott Craner

148,073
10
49
81

@findwindow R1C1 is absolute same as `Cells(1,1)`, where as R[1]C[1] is relative it is the same as `offset(1,1)` – Scott Craner Dec 09 '15 at 22:59
@DirkReichel Thank you, there were two things wrong with that part. – Scott Craner Dec 09 '15 at 23:03

score 0 · Answer 2 · answered Dec 10 '15 at 08:15

0

It looks to me like you just need:

Range("L3:W" & iVal).FormulaR1C1 = "=IF(RC5=R1C,RC1,"" "")"

answered Dec 10 '15 at 08:15

Rory

32,730
5
32
35

VBA activecell.formulaR1C1 absolute with variable

2 Answers2