Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
R1C1 | Excel Discussion (Misc queries) | |||
help on Converting R1C1 to A1 and A1 to R1C1..tia sa2 | Excel Discussion (Misc queries) | |||
help on Converting R1C1 to A1 and A1 to R1C1..tia sa2 | Excel Worksheet Functions | |||
R1C1 | Excel Discussion (Misc queries) | |||
A:1 now R1C1 | Excel Worksheet Functions |