Inserting a VB formula with a quote problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mbt6 - 07 Jan 2006 08:38 GMT

I want to insert a formula into a specific cell, something like

ActiveCell.FormulaR1C1 = "=EOMONTH (Now(), -1)"

This works fine and gives me the last day of last month.

But this doesn't work because of the "

ActiveCell.FormulaR1C1 = "=Right("0000"&c3,4)"

The formula has quotes in it, and the VB chokes as it expects only one set
of quotes.

--mike

Reply to this Message

Ron Rosenfeld - 07 Jan 2006 11:09 GMT

>I want to insert a formula into a specific cell, something like
>
[quoted text clipped - 10 lines]
>
>--mike

Double the quotes within the formula.

Also, if you are going to use the FormulaR1C1 property, you MUST use the R1C1
type of reference within the formula.

For example:

ActiveCell.FormulaR1C1 = "=Right(""0000"" & r3c3,4)"

--ron

Reply to this Message

Bob Phillips - 07 Jan 2006 11:57 GMT

or use A1

ActiveCell.Formula = "=Right(""0000""&C3,4)"

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> >I want to insert a formula into a specific cell, something like
> >
[quoted text clipped - 21 lines]
>
> --ron

Reply to this Message

Ron Rosenfeld - 07 Jan 2006 13:47 GMT

>or use A1
>
>ActiveCell.Formula = "=Right(""0000""&C3,4)"

Many ways to skin a cat in Excel!
--ron

Reply to this Message