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
|