Re: Code to Insert Formula in Cell

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <Paige@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7D01E60F-F02E-4E6D-B6F1-5887A6804594@xxxxxxxxxxxxxxxx
> Rob, thanks. Does this go into the work*** 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" <Paige@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:8CCD9615-5681-41B9-92B4-538DBB086C36@xxxxxxxxxxxxxxxx
>> > Example: I have one file with 2 worksheets (A and B). I want to have
>> > the
>> > following formula in cell B10 of work*** B: If range name RANGE1 on
>> > work*** 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 work*** 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.
>>
>>
>>


.

Quantcast