Problems inserting a formula ina acell using VBA

  • Thread starter Thread starter itassistant
  • Start date Start date
I

itassistant

Hi All,

I write this forum, trying to mfind a solution for a little problem I
have wiht my Excel spreadsheet.

I'm tryiing to insert a (not very) complex formula ina cell using the
".Formula" property of the cell, but it fails with a 1004 run-time
error.

When inserting a simple formula like the one in the example included
with the excel-help it works

Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"

but when trying ro to insert my own formula, it fails...

Worksheets("Sheet1").Range("A1").Formula =
"=IF(AH2=1;(AH1&CHAR(10));AH3) & IF(AG2=1;(AG1&CHAR(10));AG3)"

or even

Worksheets("Sheet1").Range("A1").Formula =
"=IF(AH2=1;(AH1&CHAR(10));AH3)"

The fact is that the formula is well-constructed, since if I insert
just the text (without the "=") and then insert this character manually
editing the cell in the worksheet, the formula works completely OK.

Any idea on the reasons for that problem? Is there any limitation in
the length, complexity or the Excel functions that can be used when
creating formulas that way?

Many thanks

Xabier
 
Hi Xabier,
Worksheets("Sheet1").Range("A1").Formula =
"=IF(AH2=1;(AH1&CHAR(10));AH3)"
Click to expand...

Excel VBA uses US-English formats for the formulas, so you'll need to
use commas instead of semi-colons:

Worksheets("Sheet1").Range("A1").Formula =
"=IF(AH2=1,(AH1&CHAR(10)),AH3)"

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
Back
Top