Thanks also NoSparks

I hadnt realised the xlFillDefault was not actually always the default !

But, looking at the thread jap, you puzzle me why are you doing this in VBA?

Just use Excel, then the errors are given you in real time.

If you want to use it repeatedly then write it in Excel and just load the inputs and read the outputs using VBA


Not only is it much easier to debug, it also runs much faster (Excel native calculation is much faster than doing exactly the same thing in VBA)

If you set a formula in Excel it "works out what the formula means", saves the result in Excel native language (which takes while) and calculates the answer. You then save it and it recalculates using new input values in the blink of an eye.

If you use VBA to set a formula, first it must hand over to the Excel bit (called the "command parser") to work out what your formula means, and then it must calculate the value for an answer, it cant save it because it doesnt know what vba is going to pass it.Probably doesnt make much difference if you are only going to do it once, but anything that is proessing repetitively is going to be slow.


Also logical debugging is instantaneous - it will immeditely give you #error, #value etc, rather than having to run your vba through to the suspect point

Also testing is easy, you can manually enter all of the posible entries and check that they work
every time I see .formulaArray I know it is going to be slow and unreliable, even if you get it working in the first place