r1c1

[nath]

Hi

Can anyone briefly explain to me how to use r1c1 reference
styles in VBA. I have tried the following:

ActiveSheet.range(destCol & X) = ("=IF(ISERROR(VLOOKUP(C"
& X & ",Pivot!$1:$65536,'Front End'![r9c11],0)),0,VLOOKUP
(C" & X & ",Pivot!$1:$65536,'Front End'![r9c11],0))")

Where x is the row number. This doesnt work. Do i need
to switcht the referencing to r1c1 before it will work?

TIA

Nath.

 

[Bob Phillips]

Nath,

You are basically there but you need to put the whole formulka in R1C1
notation, and tell VBA that you are setting an R1C1 formula

sFormula = "VLOOKUP(R" & x & "C3,Pivot!R1:R65536,'Front End'!r9c11,0)"
sFormula = "IF(ISERROR(" & sFormula & "),0," & sFormula & ")"
ActiveSheet.Range(destcol & x).FormulaR1C1 = "=" & sFormula