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.

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.

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.

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.

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.