Cell.Replace "XY", Variant(I) & "XY" formulla builder not functioning

I have a UDF that needs to be replaced with a built in equation (macro enabled workbooks cannot be saved on this server).

I wrote a formulla to do this but the result has long equations that cannot be passesed as strings so I had to write an assembler into the mix.
I could have sworn I had tested it and it was working but I am running it now and it isn't doing anything.

This is the assembler script is:

Please Login or Register  to view this content.

M and T are arrays of strings that contain multiplied addresses (the end formulla basically lets me use non-continuouse ranges in sumproduct)

the code executes fine no errors pop up but at the end of it the formulla in the cell is still "{=SUM(A2)/SUM(B3)}". I have stepped through it and when I get to this step all the strings are fine and the refferences are correct, VBA just isnt replacing "A2" with M(I) & "A2".

any Ideas what is causing this?
I will try again when I get back in work on monday and see if the function is working again (which I am sure it was, I have some files where this has been run and the formulla is fine on them).




I am sure someone will ask for the full module (someone always does, thinking it is rellevent) so here it is:

Please Login or Register  to view this content.

M and T are arrays of strings that contain multiplied addresses (the end formulla basically lets me non-continuouse ranges in sumproduct)

Is there logic to your layout of non-contiguous ranges? If there is.....

there is but the data that is used to form that logic is destroyed during it's creation in an earlier macro used to merge some data sheets together.

Originally Posted by Leon V (AW)

there is but the data that is used to form that logic is destroyed during it's creation in an earlier macro used to merge some data sheets together.

I thought the idea was to remove the macros... if you are using macros to merge sheets together, why not just continue to use VBA code? Or, if you are actually removing macros, just get the formulas to work by entering them instead of creating VBA code (with all the cell addresses as part of the code) to create formulas. Since it is likely that you have to do this to many many workbooks, create a template with the correct formulas and just have the macro import the required data into the template, then save a copy of the template over the existing file, and repeat as needed.

There are no macros on the sheet all macros are in an addin this script (named DeAddin) as the name implies makes the sheet readable for people without the addin (as the addin also contains some UDFs that makes these equations much easier to use.
for e.g. and this is a very small one:
with addin UDF

Formula:

Please Login or Register  to view this content.

without addin default excel method

Formula:

Please Login or Register  to view this content.

and here is a long one (this sheet isn't ready to be converted yet so I don't have a post run version):

Formula:

Please Login or Register  to view this content.

I have tested

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

and they work fine. so why can VBA not combine them????????