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.

 

[sheetspread]

I don't have Excel on this machine so can't test, but did you double "" ?

A good way to test is print the string to a cell without the equal sign and see if it matches the original formula

 

[bebo021999]

Not test but maybe there should be "=" after the evaluate?

not
Evaluate("IFERROR(

but
Evaluate("=IFERROR(

 

[johnnyL]

Thank you for the response @sheetspread, Yes, the quotes are doubled up as you can see in the first example.

Thank you for the response @bebo021999, With or without the equal sign, it performs the same ... error when trying to use 'Evaluate'.

As I stated the formula versions work fine, the evaluate is what is causing me issues with those two examples.

 

[sheetspread]

If you're sure that the formula is fine even when entered through VBA, then also try explicitly referring to the separate worksheets (you may have done this already).

 

[johnnyL]

To try and get more suggestions I decided to give an example that people can Test with.

EvaluateTest.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5
6
7	1		741						3			631.50
8	1		741-Total						-			631.50
9
Sheet2

EvaluateTest.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1														1.00	2.00	3.00	4.00	5.00	1.50
2
3	1			741															631.5
4				#VALUE!															#VALUE!
5
Sheet1

Cell Formulas
Range	Formula
D3	D3	=IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX(Sheet2!$C$7:$C$8,AGGREGATE(15,6,ROW($A$1:$A$8)/(RIGHT(Sheet2!$C$7:$C$8,5)="Total"),ROWS($1:1))),"-Total","")),SUBSTITUTE(INDEX(Sheet2!$C$7:$C$8,AGGREGATE(15,6,ROW($A$1:$A$8)/(RIGHT(Sheet2!$C$7:$C$8,5)="Total"),ROWS($1:1))),"-Total","")),"")
S3	S3	=IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--(Sheet2!$A$7:$A$8=Sheet1!$A3)*(Sheet2!$C$7:$C$8=$D3)*(Sheet2!$I$7:$I$8=S$1*2)*(Sheet2!$L$7:$L$8)),0)

Here is the sample code that I use that uses the formulas & the evaluates ... as I previously mentioned, the formulas get the correct results, the Evaluates generate errors:

Sub EvaluateTest()
'
    Dim LastRowSheet2   As Long
    Dim Part1           As String
'
    LastRowSheet2 = Sheets("Sheet2").Range("A" & Sheets("Sheet2").Rows.Count).End(xlUp).Row
'
    With Sheets("Sheet1")
        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("D3").Formula = "=IFERROR(IFERROR(VALUE(SUBSTITUTE(" & Part1 & ")),SUBSTITUTE(" & Part1 & ")),"""")"                      ' works
'
        .Range("D4").Value = Evaluate("IFERROR(IFERROR(VALUE(SUBSTITUTE(" & Part1 & ")),SUBSTITUTE(" & Part1 & ")),"""")")               ' gives #VALUE! error
'
        .Range("S3").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('Sheet2'!$A$7:$A$" & _
                LastRowSheet2 & "='Sheet1'!$A3)*('Sheet2'!$C$7:$C$" & LastRowSheet2 & "=$D3)*('Sheet2'!$I$7:$I$" & LastRowSheet2 & _
                "=S$1*2)*('Sheet2'!$L$7:$L$" & LastRowSheet2 & ")),0)"                                                                  ' works
        .Range("S4").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 & "='Sheet1'!$A3)*('Sheet2'!$C$7:$C$" & LastRowSheet2 & "=$D3)*('Sheet2'!$I$7:$I$" & LastRowSheet2 & _
                "=S$1*2)*('Sheet2'!$L$7:$L$" & LastRowSheet2 & ")),0)")                                                                 ' gives #VALUE! error
    End With
End Sub

Please let me know if you need anything else.

 

[sheetspread]

You probably should consider a more efficient data structure. Perhaps with VBA to normalize. I know everyone wants a band aid script or monster oversized formula but I advise overcoming that urge.

 

[johnnyL]

@sheetspread your last post in this thread offered zero value to the discussion. If you have nothing to offer, please keep your number of responses to that same amount.

 

[sheetspread]

Ok, I said what you didn't want to hear and may or may not be good advice. I'll try solving it your way.

 

[sheetspread]

Is there a 255 character limit in evaluate? Your formula is 279. A workaround is printing the string to D4 (preceded by an =) then converting to the value.