relative cell references in vba macro

  • Thread starter Thread starter Sarah Bendele
  • Start date Start date
S

Sarah Bendele

HELP! Here is what I have to work with and I do not like
having to figure out if 38 cells to the left is the
column I want to work with. I would rather use the
formula the way it is in the actual spreadsheet cell (=IF
(BJ2=0,BK2,BK2/BJ2)

instead I have this:
Sheets("Data").Select
Range("CT2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-37]=0,RC[-38],RC[-38]/RC
[-37])"

This is an extensive data tab for pivot tables with 100-
7000 rows and columns A-CZ.

Any help is appreciated.
 
Sarah,

Use Formula

Range("CT2").Formula = "=IF(BJ2=0,BK2,BK2/BJ2)"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thank you so much! That's so obvious, yet since I
haven't worked extensively with formulas in vba, I
wouldn't have gotten it. Works great!

Sarah
-----Original Message-----
Sarah,

Use Formula

Range("CT2").Formula = "=IF(BJ2=0,BK2,BK2/BJ2)"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Sarah Bendele said:
HELP! Here is what I have to work with and I do not like
having to figure out if 38 cells to the left is the
column I want to work with. I would rather use the
formula the way it is in the actual spreadsheet cell (=IF
(BJ2=0,BK2,BK2/BJ2)

instead I have this:
Sheets("Data").Select
Range("CT2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-37]=0,RC[-38],RC[- 38]/RC
[-37])"

This is an extensive data tab for pivot tables with 100-
7000 rows and columns A-CZ.

Any help is appreciated.
Click to expand...


.
Click to expand...
 
Back
Top