Error assigning formula to .FormulaArray

Hi,

I am looking for some help with a macro to paste an array formula into a range of cells. I know in principle the VBA is written OK as i use it in other spreadsheets. The main difference here is this one is an Array formula. When i run the code i get the error "Unable to set the FormulaArray property of the Range class".

Any ideas why i get that error? It looks OK to me. Here is the code:

Please Login or Register  to view this content.

The first problem I noticed right away was that you are using quotes within a quoted string. I am surprised this compiles, but it does. I don't know how it is parsed, but you need to double the quotes:

Please Login or Register  to view this content.

Also I didn't think the syntax looked correct so I broke it down

Please Login or Register  to view this content.

Things go off the rails in your second MATCH formula. Immediately after the Range specification ($R$10:$DE$10) you have a concatenation to to a long LOOKUP expression which will not give you a valid Range for the MATCH.

Without a lot more information I can't tell what is your objective so I can't take what you have and correct it.

Also, just my recommendation, if you are going to use the comma for the last argument in MATCH, go ahead and explicitly include the default argument of 1. It is not an error to omit it, but it provides built-in documentation for what you are doing and demonstrates intent.

Your formula is far too long for the FormulaArray property which is limited to 255 characters (yours is over 700).

Also, the way your code is written, you will get the same values in every row.

1. In VBA the Formulaarray property only accepts formulas up to 255 characters, as I said. The only workarounds are using a non-array formula if possible, or entering a shorter formula as an array, then replacing parts of it with the actual calculations you want using the Range.Replace method.

2. If you write Range("D2:O200").FormulaArray = then you will get exactly the same formula in every cell. In other words the $B2 reference will not adjust at all. It's the equivalent of you selecting that entire range, typing the formula and then pressing Ctrl+Shift+Enter. It is not the same as you array entering that into one cell and then filling across/down.

You could try something like this (untested):

Please Login or Register  to view this content.

Unfortunately the 2nd .Replace line returns a Syntax error?

Edit: corrected this by removing the brackets, but it still does not like that line.

Looks like you need to remove the first parenthesis

Please Login or Register  to view this content.

The amendment gets past the Syntax error but still debugs due to that line but it doesn't give any suggestion as to why

Edit: the error is "type mismatch"

Sorry, I didn't spot the quotes in the formula. Second one should be:

Please Login or Register  to view this content.

This code runs for me in your sample workbook:

Please Login or Register  to view this content.