VBA code :::::: Compile error: Expected : list separator or )

[Prat14]

I am trying to get the multiple results form a Range and cannot use vlookup so using INDEX Function.

My formula "=INDEX(Sheet1!$A:$I,SMALL(IF(Sheet1!$A:$A=$A$2,ROW(Sheet1!$A:$A)),ROW(2:2)),7)"
worked perfectly but not working in VBA code. I am getting "Compile error: Expected : list separator or )" and when i debug cursor goes to the last part of the code where i have ROW(2:2).

Please assist. In code i am trying to insert the value which i will get from index function into Sheet2-D6 cell.

Here is my code.
Sheets("Sheet2").Range("D6").Value = Application.WorksheetFunction.INDEX(Sheets("Sheet1").Range("$A:$I"),Application.WorksheetFunction.SMALL(Application.WorksheetFunction.IF(Sheets("Sheet1").Range("$A:$A")= Sheets("Sheet2").Range("A2").Value, Application.WorksheetFunction.ROW(Sheets("Sheet1").Range("$A:$A"))),Application.WorksheetFunction.ROW(2:2)),7)

 

[VBA Geek]

how about doing:

Sheets("Sheet2").Range("D6").Formula = _
    "=INDEX(Sheet1!$A:$I,SMALL(IF(Sheet1!$A:$A=$A$2,ROW(Sheet1!$A:$A)),ROW(2:2)),7)"

 

[Prat14]

Thank you for response VBA geek.

this is working 90% but i need to make it an array function how can i do it.

i tried Sheets("Sheet2").Range("D6").Formula = _ "{=INDEX(Sheet1!$A:$I,SMALL(IF(Sheet1!$A:$A=$A$2,ROW(Sheet1!$A:$A)),ROW(2:2)),7)}"
but it is not working

Please advise!

 

[VBA Geek]

use .formulaArray instead of .formula

 

[Prat14]

It works.
Thank you so much VBA Geek.

 

[Prat14]

Hello VBA Geek a quick question how can i make the row (2:2) Dynamic

I am using this:

For i = 1 To j


Sheets("Sheet2").Range("D" & k).FormulaArray = _
"=INDEX(Sheet1!$A:$I,SMALL(IF(Sheet1!$A:$A=$A$2,ROW(Sheet1!$A:$A)),ROW(i:i)),7)"

Next i

 

[VBA Geek]

since the ROW(i:i) in your formula below would just return every number from 1 to j as the loop runs, you can just replace it with i

For i = 1 To j


     Sheets("Sheet2").Range("D" & k).FormulaArray = _
            "=INDEX(Sheet1!$A:$I,SMALL(IF(Sheet1!$A:$A=$A$2,ROW(Sheet1!$A:$A))," & i & "),7)"


Next i

Hello VBA Geek a quick question how can i make the row (2:2) Dynamic

I am using this:

For i = 1 To j


Sheets("Sheet2").Range("D" & k).FormulaArray = _
"=INDEX(Sheet1!$A:$I,SMALL(IF(Sheet1!$A:$A=$A$2,ROW(Sheet1!$A:$A)),ROW(i:i)),7)"

Next i

 

[Prat14]

WoW ....it works like magic .. Thank you VBA Geek Really Really Appreciated.