Need some help converting VBA formula to Evaluate ...

[johnnyL]

I have a couple of VBA formulas that are causing me distress ...

    Part1 = "INDEX(Sheet2!$C$7:$C$" & LastRowSheet2 & ",AGGREGATE(15,6,ROW($A$1:$A$" & LastRowSheet2 & ")/(RIGHT(Sheet2!$C$7:$C$" & LastRowSheet2 & ",5)=""Total""),ROWS($1:1))),""-Total"","""""
    Range("D2").Formula = "=IFERROR(IFERROR(VALUE(SUBSTITUTE(" & Part1 & ")),SUBSTITUTE(" & Part1 & ")),"""")"                                                                  ' works
''    Range("D2").Value = Evaluate("IFERROR(IFERROR(VALUE(SUBSTITUTE(" & Part1 & ")),SUBSTITUTE(" & Part1 & ")),"""")")                                                           ' gives #VALUE! error

&

    Range("S2").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('LastRowSheet2'!$A$7:$A$" & LastRowSheet2 & "='Sheet3'!$A2)*('Sheet2'!$C$7:$C$" & LastRowSheet2 & "=$D2)*('LastRowSheet2'!$I$7:$I$" & LastRowSheet2 & "=S$1*2)*('LastRowSheet2'!$L$7:$L$" & LastRowSheet2 & ")),0)"         ' works
''    Range("S2").Value = Evaluate("IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('Sheet2'!$A$7:$A$" & LastRowSheet2 & "='Sheet3'!$A2)*('Sheet2'!$C$7:$C$" & LastRowSheet2 & "=$D2)*('Sheet2'!$I$7:$I$" & LastRowSheet2 & "=S$1*2)*('Sheet2'!$L$7:$L$" & LastRowSheet2 & ")),0)")  ' gives #VALUE! error

The formulas work fine, but when I try to convert the VBA formulas to Evaluate, the evaluation gives error #VALUE!.


After the evaluation problem is solved, I will need to put the evaluation results down the range, for example D2:D2000 for the first formula.

 

[johnnyL]

There is indeed a character limit of 255 for the evaluate, but I don't think that is the main issue here.

I renamed the variable 'LastRowSheet2' to 'LastRow' & that makes the second evaluate about 235 characters in length, still same result.

The second formula/evaluate is the one I am most concerned about because it is volatile and uses SUMPRODUCT on a large range instead of SUMIFS. Due to the number of formulas involved, the script is taking over a minute just to process all of the formulas.

I stink at formulas but I took a shot at using INDEX & SUMIFS to convert the second formula, It seems to give the same result, but I am not sure it would in other examples:

        .Range("S3").Formula = "=IF(INDEX($N$1:$R$" & LastRow & ",ROW(),MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMIFS('Sheet2'!$L$7:$L$" & _
                LastRow & ",'Sheet2'!$A$7:$A$" & LastRow & ",'Sheet1'!$A3,'Sheet2'!$C$7:$C$" & LastRow & ",'Sheet1'!$D3,'Sheet2'!$I$7:$I$" & _
                LastRow & ",'Sheet1'!S$1*2),0)"

 

[johnnyL]

Strangely enough, for me anyways, with the converted second formula I came up with, The following 'Evaluate' code seems to give the same result as the formula:

        MyFormula = "=IF(INDEX($N$1:$R$" & LastRow & ",ROW(),MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMIFS('Sheet2'!$L$7:$L$" & _
                LastRow & ",'Sheet2'!$A$7:$A$" & LastRow & ",'Sheet1'!$A3,'Sheet2'!$C$7:$C$" & LastRow & ",'Sheet1'!$D3,'Sheet2'!$I$7:$I$" & _
                LastRow & ",'Sheet1'!S$1*2),0)"                                                                                                 ' works
        .Range("S4").Value = Evaluate(MyFormula)

Now I really am wondering if the conversion is correct.

 

[sheetspread]

Does a VBA generated formula converted to value (without using evaluate) work?

 

[johnnyL]

Of course.