cell formula in string variable doesnt work
 

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?

cell formula in string variable doesnt 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

"James" wrote:

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?

cell formula in string variable doesnt work
 

This might work.

Range("J10").Select
ActiveCell.FormulaR1C1 = Y1,Value
Range("J11").Select
ActiveCell.FormulaR1C1 = Y2.Value


"James" wrote in message
...
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?

cell formula in string variable doesnt work
 

No, it won't. Are you trying to post the formulas or the values to the
cells?


"JLGWhiz" wrote in message
...
This might work.

Range("J10").Select
ActiveCell.FormulaR1C1 = Y1,Value
Range("J11").Select
ActiveCell.FormulaR1C1 = Y2.Value


"James" wrote in message
...
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?

cell formula in string variable doesnt work
 

James:
a) the " " messing thing up; you need """" -- a quote before a quote to
indicate the second one is be be taken literally and not as a delimiter
b) you are not using R1C1 notation
c) no need to use Select

Sub tryme()
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

End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"James" wrote in message
...
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?

cell formula in string variable doesnt work
 

Thanks!
using Range("A1").Formula worked with the added """" in there. Thanks again!



"Bernard Liengme" wrote:

James:
a) the " " messing thing up; you need """" -- a quote before a quote to
indicate the second one is be be taken literally and not as a delimiter
b) you are not using R1C1 notation
c) no need to use Select

Sub tryme()
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

End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"James" wrote in message
...
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?

cell formula in string variable doesnt work
 

On Wed, 8 Jul 2009 10:45:01 -0700, James
wrote:

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?

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