CODEWITHSUNDEEP
vbaexcel
MVZ
MVZ

Reputation: 15

VBA activecell.formulaR1C1 absolute with variable

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.

Upvotes: 0

Views: 6781

Answers (2)

Rory
Rory

Reputation: 34055

It looks to me like you just need:

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

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

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.

Upvotes: 2

Related Questions