VBA - Index and Match Code

[asid]

Hi, I'm trying to convert the following formula : =INDEX(A1:A56,MATCH(MIN(B1:B56),B1:B56,0))
to vba code but it doesn't seem to be working. I tried the following:

Application.WorksheetFunction.Index(A1:A56, Application.WorksheetFunction.Match(min(B1:B56,B1:B56, 0))

but I keep getting error message. I even tried copying the min value to a separate cell and use the following formula:
=INDEX(A1:A56,MATCH(B57,B1:B56,0)) and the corresponding code as
Application.WorksheetFunction.Index(A1:A56, Application.WorksheetFunction.Match(B57,B1:B56, 0))

but I still get the error message. Can someone please help with this one?

 

[Hui]

Asid

Firstly, Welcome to the Chandoo.org Forums

Try:

Set RngA = Worksheets("Sheet1").Range("A1:A56")
Set RngB = Worksheets("Sheet1").Range("B1:B56")

minanu = Application.WorksheetFunction.Index(RngA, Application.WorksheetFunction.Match(Application.WorksheetFunction.Min(RngB), RngB, 0))

 

[Marc L]

Hi !

See also Evaluate method …
​

 

[achu]

Hi,

Private Sub indexmatch()
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

With Worksheets("Index").Range("D5:O580")
.Formula = "=INDEX(Sheet1!$B$2:$N$576,MATCH(Index!$C5,Sheet1!$B$2:$B$576,0),MATCH(Index!D$4,Sheet1!$B$1:$N$1,0))"
.Value = .Value
End With

End Sub