Translate index/match function to vba code

[vbahelp123]

Hello i need a little help here.


how do i translate this to a vba code?


=INDEX('Workers List'!D3:D13,MATCH('Workers List'!C4,'Workers List'!C3:C13,0),1)


what i have done is


test = Application.Index(Sheets("Workers List").Range("C3;C13"), Application.Match(1, Sheets("Workers List").Range("D3:D13"), 0), 1)


But i keep getting an error, may i know whats wrong with it?

 

[JMonty]

Re: need help to translate index/match function to vba code

Try:

[COLOR=#333333]test = Application.WorksheetFunction.Index(Sheets("Workers List").Range("C3:C13"), Application.WorksheetFunction.Match(1, Sheets("Workers List").Range("D3:D13"), 0), 1)

Let us know if this helps.[/COLOR]

 

[vbahelp123]

Re: need help to translate index/match function to vba code

thanks a lot! it worked like magic!

 

[JMonty]

Re: need help to translate index/match function to vba code

You're welcome. Always remember that if you're using a built-in Excel formula in your VBA to use Application.WorksheetFunction.[built-in function here]. That's what you forgot in your code (you were just using Application).

Cheers, mate.

 

[vbahelp123]

Re: need help to translate index/match function to vba code

alright thanks a lot! i will remember that.

another question, lets say if i am to create a for loop for this. how do i change the cell referencing part (the one in bold at the bottom)?

TotalCost = 0

For i = 4 To LR Step 1
'TotalCost = TotalCost + Cells(i, col).Value * Application.WorksheetFunction.Index(Sheets("Workers List").Range("C3:C" & Cells(Rows.Count, "C").End(xlUp).Row), Application.WorksheetFunction.Match(Sheets("Workers List").cells(i,3), Sheets("Workers List").Range("D3:D13" & Cells(Rows.Count, "D").End(xlUp).Row), 0), 1)
Next i

 

[jc352]

Re: need help to translate index/match function to vba code

How would the code be adjusted to allow for no match being found? Right now it just errors out.

SAP = Application.WorksheetFunction.Index(Sheets("Roster").Range("A2:A" & Rosterrows), Application.WorksheetFunction.Match(Sheets("W4").Range("B" & y), Sheets("Roster").Range("D2:D" & Rosterrows), 0), 1)

 

[hzarry]

Re: need help to translate index/match function to vba code

Hi,
I'm trying to adjust my own index match formula into VBA as per JMonty's example above but I cannot seem to get it to work...

Here is the formula i want to convert

 =INDEX('CMRS Submission Report'!C:C,MATCH('Latest Trades'!A2&'Latest Trades'!E2&"NACK RECEIVED",'CMRS Submission Report'!D:D&'CMRS Submission Report'!F:F&'CMRS Submission Report'!G:G,0))

And here is what i have so far, but it is working.

 test = Application.WorksheetFunction.Index(Sheets("CMRS Submission Report").Range("C:C"), Application.WorksheetFunction.Match(1, Sheets("Latest Trades").Range("A:A") & Sheets("Latest Trades").Range("E:E") &"NACK RECEIVED", Application.WorksheetFunction.Match(1, Sheets("CMRS Submission Report").Range("D:D")&Sheets("CMRS Submission Report").Range("F:F")&Sheets("CMRS Submission Report").Range("G:G"),0),1)

Thanks

 

[Berglund]

Re: need help to translate index/match function to vba code

I am not seeing replies to the latest questions on this thread related to looping through a set of cells with the index/match vba code. Could someone post some code that would show how you would do the following?

Sheet1, Column A includes a number of items (J) that you want to do an Index/Match on items (I) from Sheet2, Columns A and B but I > J. Column A from each sheet has the matching information and you want to return the results from Column

Thank you in advance!

 

[FuNeS13]

Re: need help to translate index/match function to vba code

Application.WorksheetFunction.Index(Sheets("Data_Base").Range("H2:H65000"), Application.WorksheetFunction.Match(Range("A1"), Sheets("Datae_Base").Range("A2:A65000"), 0), 1)

Is something wrong with this code?

 

[JMPRuneau]

Re: need help to translate index/match function to vba code

Can you help me with the same issue, here is the information:

PeriodStart = Cells(3, 2)
InstName = Cells(1, 2)

Set sht = Sheets("Timesheet_Data")
Set pasteSht = Sheets("Time Sheet")
Worksheets("Timesheet_Data").Visible = True
Application.GoTo Reference:=Worksheets("Timesheet_Data").Cells(1, 1)
exist = Application.WorksheetFunction.Index(sht.Range("A:B")),Application.WorksheetFunction.Match(1,((sht.Range("A:A")=(pasteSht.PeriodStart))*(sht.Range("B:B")=(pasteSht.InsName))),0),1)
'exist = Application.Application.Match(PeriodStart, sht.Range("A:A"), 0)
Set allData = Worksheets("Timesheet_data").Range("A:A")