Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ), "" )" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ), "" )" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ), "" )" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ), "" )" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can I do a formula to use only the highlited cell's | Excel Worksheet Functions | |||
How do I use a referenced cell's formula instead of formula output? | Excel Worksheet Functions | |||
Setting the magnification to a range of cell's | Excel Discussion (Misc queries) | |||
How do I use a cell's text, "A1", and put it in a formula? | Excel Worksheet Functions | |||
Formula that will allow you to add one to cell's value by clickin. | Excel Worksheet Functions |