How to use r1c1 to reference negative absolute column?

[QuantBurner]

Hi,
I need to do this:
With .Range(.Cells(j + 1, 14), .Cells(j + 1, 18))
.FormulaR1C1 = "=r[-1]c[]/indirect(""I""&match(""gbp/""&r[-1]c-1,c-5:c-5,0)+1)"
End With

Which gives an error because of the c-1

Any tips?

Cheers

 

[Andrew Poulsom]

Did you try?

c[-1]

 

[QuantBurner]

It occurs to me I can set a couple of variables to negative values like this:
dim a,b as integer
a=-1
b=-5
.
.
....c" & a & "....

But, there's gotta be an easier way, eh?

 

[QuantBurner]

I need an absoulte reference. The [-1] will give a relative reference..
Thanks though.

 

[Andrew Poulsom]

For an absolute reference use C1 for column A, C2 for column B etc. If you record a macro while entering the formula manually you will get the syntax.

 

[QuantBurner]

Oh, right! Thanks. Feeling silly now.