Converting AGGREGATE formula to VBA code

Samuelkmh

New Member
Joined
Jul 2, 2019
Messages
4
I have this formula that works but I can seem to convert it to a VBA code. When I run the code, there is no error, but no value appears. Appreciate your help.

My formula:
INDEX('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:CR298,AGGREGATE(14,6,(ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:D298)-ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78)+1)/(('[Balanced Portfolio AA_280219.xlsx]Summary Table'!B78:B298 =D3)*(INDEX('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:CR298,0,MATCH(B2,'[Balanced Portfolio AA_280219.xlsx]Summary Table'!D5:CR5,0))<>"")),1),MATCH(B2,'[Balanced Portfolio AA_280219.xlsx]Summary Table'!D5:CR5,0))


My code:
Set r2 = Application.WorksheetFunction.Index(sh1.Range("D78:CR298"), Application.WorksheetFunction.Aggregate(14, 6, ((sh1.Range("D78:D298").Row) - (sh1.Range("D78").Row) + 1) / ((sh1.Range("B78:B298") = comboSh1.Range("D3")) * (Application.WorksheetFunction.Index(sh1.Range("D78:CR298"), 0, Application.WorksheetFunction.Match(comboSh1.Range("B2"), sh1.Range("D5:CR5"), 0)) <> "")), 1), Application.WorksheetFunction.Match(comboSh1.Range("B2"), sh1.Range("D5:CR5"), 0))


I defined sh1 as '[Balanced Portfolio AA_280219.xlsx]Summary Table' from another workbook
and comboSh1 as the sheet in the active workbook.

I am trying to find obtain a value that has duplicated variables and hence I need this function.

I think there is something wrong when i tried to convert

AGGREGATE(14,6,(ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:D298)-ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78)+1)/(('[Balanced Portfolio AA_280219.xlsx]Summary Table'!B78:B298 =D3)

into

Application.WorksheetFunction.Aggregate(14, 6, ((sh1.Range("D78:D298").Row) - (sh1.Range("D78").Row) + 1) / ((sh1.Range("B78:B298") = comboSh1.Range("D3"))

not sure how one range can be = to another range.
 

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top