cell formula in string variable doesnt work

  • Thread starter Thread starter James
  • Start date Start date
J

James

I have:
Y1 = "=IF($D24=0,"",$D24-$I$4*TAN(RADIANS(L10)/2))"
Y2 = "=IF($D24=0,"",$D24-$I$4*2*TAN(RADIANS(L10)/2))"

then try and use:
Range("J10").Select
ActiveCell.FormulaR1C1 = Y1
Range("J11").Select
ActiveCell.FormulaR1C1 = Y2

Why doesnt this work?
 
Hi,

Try this and note the doubled up internal quotes in the formula

Y1 = "=IF($D24=0,"""",$D24-$I$4*TAN(RADIANS(L10)/2))"
Y2 = "=IF($D24=0,"""",$D24-$I$4*2*TAN(RADIANS(L10)/2))"
Range("J10").Formula = Y1
Range("J11").Formula = Y2

Mike
 
I have:
Y1 = "=IF($D24=0,"",$D24-$I$4*TAN(RADIANS(L10)/2))"
Y2 = "=IF($D24=0,"",$D24-$I$4*2*TAN(RADIANS(L10)/2))"

then try and use:
Range("J10").Select
ActiveCell.FormulaR1C1 = Y1
Range("J11").Select
ActiveCell.FormulaR1C1 = Y2

Why doesnt this work?
Click to expand...

1. Your string is not producing what you think it is. When VBA sees a double
quote within quotes, it translates that as a single quote. So your Y1 string
comes out as

=IF($D24=0,",$D24-$I$4*TAN(RADIANS(L10)/2))

If you want it to come out correctly, you need to have TWO (2) double quotes:
ie.

y1 = "=IF($D24=0,"""",$D24-$I$4*TAN(RADIANS(L10)/2))"

2. You are using the FormulaR1C1 of the Range object, but your formula is not
in that form -- it does not have an R1C1 style references.

You can either change your formula to use R1C1 style references; or use the
Formula property of the Range object.
--ron
 
Back
Top