View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Sandy Sandy is offline
external usenet poster
 
Posts: 355
Default HELP!! Change formulas programmatically -- all I get is text!

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"?
--
Sandy


"Baapi" wrote:

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
--
Baapi


"Sandy" wrote:

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?
--
Sandy


"Baapi" wrote:

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 & "))")


--
Baapi


"Sandy" wrote:

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)

--
Sandy