Set cell formulas using code? how?

  • Thread starter Thread starter Reedi
  • Start date Start date
R

Reedi

Hi,

I see from the Excel help that you can set formulas, but when I tried to set
the following formula:

Worksheets("Sheet1").Range("A1").Formula = "=+IF(B1 <> "",(B1-1)*1000 +
(1000-C1),"")"

it didn't work? I get run-time error 1004 "Application defined or object
defined error"

Any ideas?

Thanks in advance,

Reedi
 
First, ditch the =+. That marks you as a Lotus-holdout.

The problem you're having is that quotation marks in VBA designate
the start and end of a string of text. To refer to quotation marks
*within* a string of text, you need to double them (""):

Worksheets("Sheet1").Range("A1").Formula = _
"=IF(B1<> """", (B1-1)*1000+(1000-C1), """")"

which could be more simply written:

Worksheets("Sheet1").Range("A1").Formula = _
"=IF(B1<> """", B1 * 1000 - C1, """")"
 
Back
Top