Setting a cell's formula with VBA
 

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

Setting a cell's formula with VBA
 

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)


wrote in message
...
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 ), "" )"

Setting a cell's formula with VBA
 

..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 ), "" )

--
Regards,
Tom Ogilvy



wrote in message
...
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 ), "" )"

Setting a cell's formula with VBA
 

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.


" wrote:

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