HELP!! Change formulas programmatically -- all I get is text!

[Sandy]

Hello -

I need to change the formulas in a cell depending on what button the user
clicks; DIV1 or DIV2. The following formula is in the cell at startup:
=IF(G35="No",REF!F35,IF(G35="n/a",REF!F35,""))

If the user clicks DIV2, the formula should change to:
=REF!C200
I tried the following and it just puts text in the cell:
Sheet21.Range("I35").Formula = "=REF!C200"

If the user clicks DIV1, I tried the following, which also just puts the
text of the formula in the cell:
Sheet21.Range("I35").Formula =
"=IF(G35=""No"",REF!F35,IF(G35=""n/a"",REF!F35,""""))"

What am I doing wrong?

Any help will be greatly appreciated!! The users expect me to miraculously
fix this by tomorrow a.m. YIKES! (There are about 25 formulas, not related
to each other)

 

[Baapi]

Try using cell references instead of absolute values. This will solve your
problems of Quotes"""""""

Something like:
Cells(R, C).Formula = ("=SUMPRODUCT(--(" & RngP5.Address(External:=True) &
"=" & Cr1.Address & _
"),--(" & RngP1.Address(External:=True) & ">=" & Cr7.Address &
"),--(" & RngP1.Address(External:=True) & "<=" & Cr8.Address & _
"),--(" & RngP3.Address(External:=True) & "=" & Cr3.Address &
"),--(" & RngP2.Address(External:=True) & "=" & Cr2.Address & "))")

 

[Sandy]

Hello Baapi!

Thanks for your response.

I'm lost with your code. Can you translate that using my code?

PROBLEM: My code puts the actual text of the formula into the cell instead
of the formula; i.e. the formula should appear in the formula bar only. My
code fills the cell with the text of the formula word for word.

Any thoughts?

 

[Baapi]

I would write thi way.
Store "No" in A1 (Example)
Store "n/a" in B1 (Example)
Store ""(Null) in C1 (Example) 'In other words, keep the cell blank

Sub PutFormula()
Dim YesNo As Range, na As Range, blk As Range
Set YesNo = Range("A1")
Set na = Range("B1")
Set blk = Range("C1")
Sheet1.Range("I35").Formula = "=IF(G35=" & YesNo & ",Sheet2!F35,IF(G35=" &
na & ",Sheet2!F35," & blk & "))"
End Sub

 

[Sandy]

Hi Baapi -

I tried an example separate from my app and I had no problem getting it to
work, actually with my code as it was written (double quotes and all).

I'm wondering if there is something happening due to a security issue. Do
you know how to explicitly unlock a cell for changing a formula, or I suppose
a better way to put it is "allow formula changes"?

 

[Simon Murphy]

Sandy
Your code works fine for me
Make sure all the sheets exist and the worksheet is unprotected, or the cell
is formatted as not locked
Also make sure display is not set to formulas
(tools>>options>>view>>formulas) and they are not formatted as text
cheers
Simon

 

[Dave Peterson]

How about:

with sheet21.range("I35")
.numberformat = "General"
.formula = "=ref!c200"
end with

It sounds like excel is changing that cell to text for some reason.

 

[Sandy]

Thank you to everyone!

The problem was in the REF sheet. All of the cells were formatted as text,
so everytime that a cell was brought into the sheet that referred to the REF,
it brought the text formatting along with it.

Thanks again!