How to stop Evaluate Formula Range from spitting out the same result for the entire range

[johnnyL]

This whole 'Evaluate' function in VBA is really starting to tick me off.

I currently can't get it to stop spitting out the same first result down the entire range of the U column.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1														1	2	3	4	5	1.5
2
3				Range Formula															Range Formula	Evaluate Cell Formulas	Evaluate Range Formula
4	1			741															631.5	631.5	631.5
5	3			841															100.25	100.25	631.5
6	5			941															222.33	222.33	631.5
7
Sheet1

Cell Formulas
Range	Formula
D4:D6	D4	=IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX(Sheet2!$C$7:$C$12,AGGREGATE(15,6,ROW($A$1:$A$12)/(RIGHT(Sheet2!$C$7:$C$12,5)="Total"),ROWS($1:1))),"-Total","")),SUBSTITUTE(INDEX(Sheet2!$C$7:$C$12,AGGREGATE(15,6,ROW($A$1:$A$12)/(RIGHT(Sheet2!$C$7:$C$12,5)="Total"),ROWS($1:1))),"-Total","")),"")
S4:S6	S4	=IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--(Sheet2!$A$7:$A$12=$A4)*(Sheet2!$C$7:$C$12=$D4)*(Sheet2!$I$7:$I$12=S$1*2)*(Sheet2!$L$7:$L$12)),0)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5
6
7	1		741						3			631.5
8	2		741-Total						-			631.5
9	3		841						3			100.25
10	4		841-Total						-			100.25
11	5		941						3			222.33
12	6		941-Total						-			222.33
13
Sheet2

Option Explicit

Sub EvaluateTest()
'
    Dim LastRow         As Long
    Dim MyFormula       As String
    Dim Part1           As String
    Dim WS1             As String
    Dim WS2             As String
'
    WS1 = "Sheet1"
    WS2 = "Sheet2"
'
    LastRow = Sheets("Sheet2").Range("A" & Sheets("Sheet2").Rows.Count).End(xlUp).Row
'
    With Sheets(WS1)
        .Activate
'
        Part1 = "INDEX(Sheet2!$C$7:$C$" & LastRow & ",AGGREGATE(15,6,ROW($A$1:$A$" & LastRow & ")/(RIGHT(Sheet2!$C$7:$C$" _
                & LastRow & ",5)=""Total""),ROWS($1:1))),""-Total"","""""
        .Range("D4:D6").Formula = "=IFERROR(IFERROR(VALUE(SUBSTITUTE(" & Part1 & ")),SUBSTITUTE(" & Part1 & ")),"""")"              ' Range (D4:D6) formula works
'
'---------------------------------------------------------------------------------------------------------------------------
'
        .Range("S4:S6").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('Sheet2'!$A$7:$A$" & _
                LastRow & "=$A4)*('Sheet2'!$C$7:$C$" & LastRow & "=$D4)*('Sheet2'!$I$7:$I$" & LastRow & _
                "=S$1*2)*('Sheet2'!$L$7:$L$" & LastRow & ")),0)"                                                                    ' Range (S4:S6) formula works
'
'---------------------------------------------------------------------------------------------------------------------------
'
        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 & ",$A4,'Sheet2'!$C$7:$C$" & LastRow & ",$D4,'Sheet2'!$I$7:$I$" & _
                LastRow & ",S$1*2),0)"
        .Range("T4").Value = Sheets(WS1).Evaluate(MyFormula)                                                                        ' Evaluate for cell (T4) works
'
'---------------------------------------------------------------------------------------------------------------------------
'
        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 & ",$A5,'Sheet2'!$C$7:$C$" & LastRow & ",$D5,'Sheet2'!$I$7:$I$" & _
                LastRow & ",S$1*2),0)"
        .Range("T5").Value = Sheets(WS1).Evaluate(MyFormula)                                                                        ' Evaluate for cell (T5) works
'
'---------------------------------------------------------------------------------------------------------------------------
'
        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 & ",$A6,'Sheet2'!$C$7:$C$" & LastRow & ",$D6,'Sheet2'!$I$7:$I$" & _
                LastRow & ",S$1*2),0)"
        .Range("T6").Value = Sheets(WS1).Evaluate(MyFormula)                                                                        ' Evaluate for cell (T6) works
'
'---------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------------------------------------------------------
'
        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 & ",$A4,'Sheet2'!$C$7:$C$" & LastRow & ",$D4,'Sheet2'!$I$7:$I$" & _
                LastRow & ",S$1*2),0)"
        .Range("U4:U6").Value = Sheets(WS1).Evaluate(MyFormula)                                                                     ' Evaluate for range (U4:U6) gives same result thru range ???
    End With
End Sub

Can anyone assist how to 'fix' the U column?


Another thing I noticed while trying to use the 'Evaluate' function in this example ... If the active cell in the active sheet is below 'LastRow' then the Evaluate formula(s) result in '#REF!' error. WTF?

Anyone care to respond?

 

[johnnyL]

My eyes are bleeding from googling a solution to this. Everything I have tried hasn't worked.

I was hoping to find a solution to this for two different reasons.
1) It is ticking me off, not knowing how to fix it.
2) I was hoping to do some time testing vs alternative methods.

 

[johnnyL]

I just registered @ stackoverflow , the link to the same question is here