Loop with Array Formula in VBA

[rubikscube991]

Hii everyone in need to loop through a range and get the values in 2 column (using index match maybe.)

My current formula works for 1 cell But i don't know how to use a for loop with it?

Also when i run the code it pastes the formula in it. I don't want any formula pasted in the cells, but only values. (If that is possible)

Please help! thanks in advance.

 

[Marc L]

Hi !

No needs loop ! Just a correct formula :​

Sub Demo1()
    With [I8:I11]
        .Cells(1).FormulaArray = "=INDEX($B$6:$H$45,MATCH(G8&H8,$C$6:$C$45&$B$6:$B$45,0),3)"
        .Cells(1).AutoFill .Cells
        .Formula = .Value
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[p45cal]

and since you're probably wanting to do this for two columns:

Sub Demo1()
    With [I8:J11]
        .Cells(1).FormulaArray = "=INDEX(D$6:D$45,MATCH($G8&$H8,$C$6:$C$45&$B$6:$B$45,0))"
        .Cells(1).AutoFill .Rows(1)
        .Rows(1).AutoFill .Cells
        .Formula = .Value
    End With
End Sub

 

[rubikscube991]

Thankyou mark And Pascal for your very quick response & sorry for my late Thanks.

Thankyou.

Actually this is just a part of my other problem. Autofill is not an option for me. i have to solve it using "for loop" method only. But anyways thanks.

 

[Marc L]

So you must crystal clear explain your algorithm
as a loop is often the worst way !​

 

[p45cal]

Autofill is not an option for me. i have to solve it using "for loop" method only.

At first sight this statement would appear to be ludicrous… unless, that is, it's part of an assignment/homework?