Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Insert Formula in Cell
Example: I have one file with 2 worksheets (A and B). I want to have the
following formula in cell B10 of worksheet B: If range name RANGE1 on worksheet A = 12, then insert the following text - "this is a test"; otherwise, insert a 0. I had: Worksheets("B").Range("B10").Formula = "=if('A'!RANGE1="12","this is a test",0)" but kept getting error messages. Tried with and without all types of quotes and multiple other things, but still got various error messages like application defined error, syntax error, or range property, etc. Am I reference this correctly (obviously not). Also, where would this go - into the module for worksheet B? What type of declaration (calculate?); I may not have that correct either (tried all different kinds). Appreciate any help that can be provided. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Insert Formula in Cell
Hi Paige,
This works for me: Worksheets("B").Range("B10").Formula = _ "=if(A!RANGE1=12,""this is a Test"",0)" -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "Paige" wrote in message ... Example: I have one file with 2 worksheets (A and B). I want to have the following formula in cell B10 of worksheet B: If range name RANGE1 on worksheet A = 12, then insert the following text - "this is a test"; otherwise, insert a 0. I had: Worksheets("B").Range("B10").Formula = "=if('A'!RANGE1="12","this is a test",0)" but kept getting error messages. Tried with and without all types of quotes and multiple other things, but still got various error messages like application defined error, syntax error, or range property, etc. Am I reference this correctly (obviously not). Also, where would this go - into the module for worksheet B? What type of declaration (calculate?); I may not have that correct either (tried all different kinds). Appreciate any help that can be provided. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Insert Formula in Cell
Rob, thanks. Does this go into the worksheet module? Under what type of
declaration? I'm still having problems and think it must be because I don't have it in the right type of module or the correct declaration. "Rob Bovey" wrote: Hi Paige, This works for me: Worksheets("B").Range("B10").Formula = _ "=if(A!RANGE1=12,""this is a Test"",0)" -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "Paige" wrote in message ... Example: I have one file with 2 worksheets (A and B). I want to have the following formula in cell B10 of worksheet B: If range name RANGE1 on worksheet A = 12, then insert the following text - "this is a test"; otherwise, insert a 0. I had: Worksheets("B").Range("B10").Formula = "=if('A'!RANGE1="12","this is a test",0)" but kept getting error messages. Tried with and without all types of quotes and multiple other things, but still got various error messages like application defined error, syntax error, or range property, etc. Am I reference this correctly (obviously not). Also, where would this go - into the module for worksheet B? What type of declaration (calculate?); I may not have that correct either (tried all different kinds). Appreciate any help that can be provided. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Insert Formula in Cell
Hi Paige,
It can go almost anywhere, it just needs to be run in order to work. The simplest method is to add a regular code module to your project and put something like the following procedure into it: Public Sub EnterFormula() Worksheets("B").Range("B10").Formula = _ "=if(A!RANGE1=12,""this is a Test"",0)" End Sub You can then run the EnterFormula procedure directly through the Tools/Macro/Macros menu, you can assign it directly to a Button object from the Forms toolbar or you can call it from any number of event procedures. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "Paige" wrote in message ... Rob, thanks. Does this go into the worksheet module? Under what type of declaration? I'm still having problems and think it must be because I don't have it in the right type of module or the correct declaration. "Rob Bovey" wrote: Hi Paige, This works for me: Worksheets("B").Range("B10").Formula = _ "=if(A!RANGE1=12,""this is a Test"",0)" -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "Paige" wrote in message ... Example: I have one file with 2 worksheets (A and B). I want to have the following formula in cell B10 of worksheet B: If range name RANGE1 on worksheet A = 12, then insert the following text - "this is a test"; otherwise, insert a 0. I had: Worksheets("B").Range("B10").Formula = "=if('A'!RANGE1="12","this is a test",0)" but kept getting error messages. Tried with and without all types of quotes and multiple other things, but still got various error messages like application defined error, syntax error, or range property, etc. Am I reference this correctly (obviously not). Also, where would this go - into the module for worksheet B? What type of declaration (calculate?); I may not have that correct either (tried all different kinds). Appreciate any help that can be provided. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Insert Formula in Cell
Perfect! Thanks so much Rob. Have a great day......Paige
"Rob Bovey" wrote: Hi Paige, It can go almost anywhere, it just needs to be run in order to work. The simplest method is to add a regular code module to your project and put something like the following procedure into it: Public Sub EnterFormula() Worksheets("B").Range("B10").Formula = _ "=if(A!RANGE1=12,""this is a Test"",0)" End Sub You can then run the EnterFormula procedure directly through the Tools/Macro/Macros menu, you can assign it directly to a Button object from the Forms toolbar or you can call it from any number of event procedures. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "Paige" wrote in message ... Rob, thanks. Does this go into the worksheet module? Under what type of declaration? I'm still having problems and think it must be because I don't have it in the right type of module or the correct declaration. "Rob Bovey" wrote: Hi Paige, This works for me: Worksheets("B").Range("B10").Formula = _ "=if(A!RANGE1=12,""this is a Test"",0)" -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "Paige" wrote in message ... Example: I have one file with 2 worksheets (A and B). I want to have the following formula in cell B10 of worksheet B: If range name RANGE1 on worksheet A = 12, then insert the following text - "this is a test"; otherwise, insert a 0. I had: Worksheets("B").Range("B10").Formula = "=if('A'!RANGE1="12","this is a test",0)" but kept getting error messages. Tried with and without all types of quotes and multiple other things, but still got various error messages like application defined error, syntax error, or range property, etc. Am I reference this correctly (obviously not). Also, where would this go - into the module for worksheet B? What type of declaration (calculate?); I may not have that correct either (tried all different kinds). Appreciate any help that can be provided. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula, Macro, or VBA code to find and insert | Excel Worksheet Functions | |||
Insert text from one cell into formula in another cell. | Excel Discussion (Misc queries) | |||
Code To Insert Cell Reference | Excel Programming | |||
Need code to insert cell value | Excel Programming | |||
need code to insert cell value | Excel Programming |