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.
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.