Setting a cell's formula with VBA

[panoffp]

I have a cell defined with the following formula

=IF($E$5="SHP", IF(K8="skid", J8*I8/1000*H8, J8*I8*H8 ), "" )

My intent is to use the formaula to provide a calucated estimate based on
the values of cells E5 and K8. The user would then be free to accept the
estimate, or to revise it. The problem is that when the value is changed,
the formula is lost! So, I am attempting to add the formula to the cell
via a macro. I have tried numerous combinations concatenating quotes and
strings but cannot seem to get it right. What visually looks good yields
compiler errors like missing end of statement. What satisfies the
compiler, corrupts the above formula's intent. Any and all suggestions
would be greatly appreciated. Thanks in advance!

Here is what I currently have that is giving the error - Compile error:
Expected: end of statement

Range("L8").Formula = "IF($E$5="SHP", IF(K8="skid",
J8*I8/1000*H8, J8*I8*H8 ), "" )"

 

[Bob Phillips]

Try this

Range("L8").Formula = "=IF($E$5=""SHP"", IF(K8=""skid"", J8*I8/1000*H8,
J8*I8*H8 ), """" )"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

..Formula = "=IF($E$5=""SHP"", IF(K8=""skid"", J8*I8/1000*H8, J8*I8*H8 ),
"""" )"

Tested in the Immediate window:

? "=IF($E$5=""SHP"", IF(K8=""skid"", J8*I8/1000*H8, J8*I8*H8 ), """" )"
=IF($E$5="SHP", IF(K8="skid", J8*I8/1000*H8, J8*I8*H8 ), "" )

 

[Guest]

Hi

Add an equal sign between the "and IF($E$5.... i.e.

Range("L8").Formula = "=IF($E$5="SHP", IF(K8="skid",
J8*I8/1000*H8, J8*I8*H8 ), "" )"

Also another way , if the formula is working OK, start your macro recorder,
select cell L8 then Insert > Function > OK and stop recorder.
locate the recorded macro, cut and paste the line or lines into your code.
Note! Code will be in R1C1 format.

Regards
Bob C.

 

[Guest]

******************************
a calucated estimate based on
the values of cells E5 and K8. The user would then be free to accept the
estimate, or to revise it. The problem is that when the value is changed,
the formula is lost!
********************************

My question: What value can the user change?
I assume that E5, K8 and L8 are LOCKED cells - not allowing the user
to change them (Also assuming that E5 and K8 have formulas that
derive their values from some other user -input cells ---which ARE NOT
locked. If you design you sheet in such a manner, your formula in
cell L8 will not be lost when the user changes an input cell.

Hope this helps