r1c1
 

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.

r1c1
 

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


--

HTH

Bob Phillips

"nath" <n@n wrote in message ...
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.