A1 reference inside RC formula?

[Elysium4Eternity]

Hello, I'm currently working on a line of code where I need to reference in A1 style inside a RC formula.

If Range("F" & i).Value = "" Then    For j = 2 To 4001 Step 4
    Range("L" & i).FormulaR1C1 = "=((rc[-2]-r[-2]c[-2])*("AH14")*24)/1000)*rc[-6]"
    Next j

The cell AH14 is the one I want to refer to, but this gives me an error. Is there a way to solve this?

 

[steve the fish]

Yes you use a RC reference.

R14C34

 

[Elysium4Eternity]

Yes I understand that, but that line of code is inside another loop where the position changes from where u are referring from.

 

[steve the fish]

You need to be a bit clearer. You will be able to do what you want but only in either A1 or RC. You cant mix them.

 

[Elysium4Eternity]

Full sub:

Sub Financiël_Gevel()


Dim i As Long
Dim j As Long
Dim k As Long


For i = 4 To 4001 Step 4


If Range("F" & i).Value = "" Then
For j = 2 To 4001 Step 4
Range("L" & i).FormulaR1C1 = "=(((rc[-2]-r[2]c[-2])*Range("AH14")*24)/1000)*rc[-6]"
Next j


Else:
For k = 2 To 4001 Step 4
Range("L" & i).FormulaR1C1 = "=(((rc[-2]-r[2]c[-2])*Range("AH14")*24)/1000)*r[2]c[-6]"
Next k


End If


Next i


End Sub

What I'm trying to do is checking if there is a value in the 4th cell of the F column, and extending this to row 4001. If there is no value in that cell then the code needs to calculate on value X in the F column and put the answer in the 2nd row L column. If there is a value in the 4th cell of the F column, the calculation needs to be executed with value Y in the F column. I hope this makes any sense...

 

[RoryA]

If AH14 is fixed, as appears to be the case, you can just use R14C34 as Steve said earlier.

 

[Elysium4Eternity]

If AH14 is fixed, as appears to be the case, you can just use R14C34 as Steve said earlier.

I'm still fairly new to using RC, but I get this now. This does the job, thanks!

 

[RoryA]

Glad we could help.

 

[Rick Rothstein]

I'm still fairly new to using RC, but I get this now. This does the job, thanks!

For future reference, since FormulaR1C1 is a text string, you can let VB figure out the R1C1 address for you. If you are getting the "AH14" address from an outside source as that text string, then (using one of your code lines for example purposes) you could do it this way...

[table="width: 500"]
[tr]
	[td]Range("L" & i).FormulaR1C1 = "=(((rc[-2]-r[2]c[-2])*" & Range("AH14").Address(, , xlR1C1) & "*24)/1000)*rc[-6]"[/td]
[/tr]
[/table]

If, on the other hand, you have a Range variable that is being set to cell AH14 earlier in the code, let's say that variable is named Rng, then you could do it this way...

[table="width: 500"]
[tr]
	[td]Range("L" & i).FormulaR1C1 = "=(((rc[-2]-r[2]c[-2])*" & Rng.Address(, , xlR1C1) & "*24)/1000)*rc[-6]"[/td]
[/tr]
[/table]