Array formula in macros

**Bafana** · 04-20-2007, 09:39 AM

I am trying to set up a macro that calculates balances per individual per month. When manually setting up the spreadsheet the array formula has returned the correct result but I am getting an error when the formula is included in a macro.

The error message returned is:
"Run-time error '1004':
Unable to set the FormulaArray property of the Range class"

The relevant section of code is:
(please note that I have manually wrapped the code below as when I tried to use the underscore character to wrap the formula I get an immediate syntax error)
The logic of the formula below is that if the values in columns E & F are equal then no value is required (the starting IF stmt). If the values are not equal then the formula will return values from 3 data sheets.

Please Login or Register  to view this content.

Hopefully someone can make me

**Leith Ross** · 04-20-2007, 05:09 PM

Hello Bafana,

A Formula Array can only be entered into an array of contiguous cells. Range("G2") is a single cell and doesn't constitute an array, hence the error. To enter a formula into single cell use the Formula property.

Sincerely,
Leith Ross

**Bafana** · 04-23-2007, 04:40 AM

I have tried your suggestion but it has not helped. Using the exact same syntax as shown in the original post, I have another macro in the same workbook that works - the only difference being that the formula listed combines an IF stmt and multiple array components.

I have tried segmenting the formula into its component parts and the macro will then run correctly. It therefore seems that I am either missing something when combining the individual components into a summarised formula or a macro somehow can't recognise the formula which when entered directly into the spreadsheet, Excel will compute correctly.

Array formula in macros

LinkBack

Thread Tools

Rate This Thread

Display

Array formula in macros

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions