Long Array Formula in VBA (Help)

Hi,

After some help with a long array formula in VBA. I have never array formulas in VBA before but from what I've read they are limited to 255 characters. To overcome this they must be reduced in length using the "replace" function.

Using examples, I came up with the below but the code fails when I run at the ".FormulaArray = theFormulaPart1" point with the Run-time error 1004 "Unable to set the ForumulaArray property of the arrange class"

Is anybody able to shed any light as to where I'm going wrong?

Thanks in advance,
Matt

Formula:

Please Login or Register  to view this content.

Hi,

Please edit your post to use code tags.

Your first part appears to be missing a closing bracket.

Thanks.

As I mentioned, your theFormulaPart1 formula has a closing bracket missing somewhere- determined purely by counting.

Also, unless you are actually using R1C1 referencing yourself, you either need the replacement sections to be in A1 format, or you will need the code to switch to R1C1, make the replacements, then switch back to A1.