FormulaArray size in macro

[JonPugh]

I am trying to record a macro and include a formula array. However I get the error 'Unable to Record". The worksheet does accept it but it won't record to the macro.

From the Microsoft Knowledge Base it says that VBA can't pass more than 255 characters in a string!! Could anybody help me rewrite this formula to work so that I can pass it in a macro?

The FormulaArray is

{=SUM(IF('7 Day Follow Up'!H6:H46&'7 Day Follow Up'!F6:F46="StandardCedars",IF('7 Day Follow Up'!O6:O46<>"",IF('7 Day Follow Up'!O6:O46-'7 Day Follow Up'!G6:G46<8,1,0),0),IF('7 Day Follow Up'!H6:H46&'7 Day Follow Up'!F6:F46="StandardLinden",IF('7 Day Follow Up'!O6:O46<>"",IF('7 Day Follow Up'!O6:O46-'7 Day Follow Up'!G6:G46<8,1,0),0),0)))}

Thanks in advance for any help

Jonathan

[shg]

From the Microsoft Knowledge Base it says that VBA can't pass more than 255 characters in a string!!

That's a limitation of the macro recorder, not VBA.

You could assign some named ranges, and change the formula to, for example,
=SUM(IF(HHH & FFF = "StandardCedars", IF(OOO <> "", IF(OOO - GGG < 8, 1, 0), 0), IF(HHH & FFF = "StandardLinden", IF(OOO <> "", IF(OOO - GGG < 8, 1, 0), 0), 0) ) )

[JonPugh]

Thanks SHG,

I am quite the novice at Excel (usually programme databases) so am constantly learning.

Now works great.

Jonathan