Index & Match in VBA

[bhalbach]

How do I convert the following formula to index match in VBA? I cannot wrap my head around this...I am trying to learn but im struggling.

=INDEX(rngLabourRates,MATCH(D13,rngLabour),0)

I have made the following vlookup code work to accomplish what i want but it returns an error when there is a blank in D13:D27 range of TskSheet.
Sub VlookupLabourRate()

For i = 13 To 27
Worksheets("TskSheet").Cells(i, 8).Value = Application.WorksheetFunction.VLookup(Worksheets("TskSheet").Cells(i, 4).Value, Worksheets("Labour").Range("D:P"), 13, 0)
Next

End Sub

I think that Index Match is what I should be using but I am open to any suggestions on how to do this so it returns my results very quickly. Should I be considering doing this with an array? Speed is important.

 

[Marc L]

Never ever use WorksheetFunction except if you want to crash your VBA procedure when the function raises an error like MATCH, VLOOKUP, whatever !​

​

Just use Application rather than WorksheetFunction …​

​

Use a Variant variable to store the function result like V = Application.Match(… and check it via VBA functions IsError or IsNumeric for example.​

​

As worksheet functions can be used via Evaluate - a VBA help must see - like [INDEX(rngLabourRates,MATCH(D13,rngLabour),0)] …​

 

[bhalbach]

Never ever use WorksheetFunction except if you want to crash your VBA procedure when the function raises an error like MATCH, VLOOKUP, whatever !​

​

Just use Application rather than WorksheetFunction …​

​

Use a Variant variable to store the function result like V = Application.Match(… and check it via VBA functions IsError or IsNumeric for example.​

​

As worksheet functions can be used via Evaluate - a VBA help must see - like [INDEX(rngLabourRates,MATCH(D13,rngLabour),0)] …​

So is the code for the vlookup the same...just replace the Application.WorksheetFunction?

I am really new to this and trying to figure it. What would the code look like?

 

[bhalbach]

So is the code for the vlookup the same...just replace the Application.WorksheetFunction?

I am really new to this and trying to figure it. What would the code look like?

I got this to work but it returns a #N/A when there is a blank.

What would the entire code look like to avoid this. Sorry I don.t just want you to provide code...I am just very new to this and trying to learn.

 

[bhalbach]

This works but I get a #N/A if it encounters a blank in column D

Sub VlookupLabourRate()


For i = 13 To 27
Worksheets("TskSheet").Cells(i, 8).Value = Application.VLookup(Worksheets("TskSheet").Cells(i, 4).Value, Worksheets("Labour").Range("D:P"), 13, 0)
Next

End Sub

 

[Marc L]

​

Yes the same error using the same function in a cell formula when nothing matches ! Try …​

​

So just check before your column D or as I yet explained in post #2 how to check the result when using Application rather than WorksheetFunction …​

 

[offthelip]

The way you are trying to use worksheet functions to do lookups in VBA is not a very efficient use of VBA. Accessing the worksheet from vba takes relatively long time. Accessing one single cell takes about as long as accessing a whole range, your code makes three accesses to worksheet on every loop. Since your loop in only a 14 rows this is probably not important, but if it was a few thousand rows your code would be very slow. You said you wanted the results returned quickly. Using variant array is usually about 1000 times faster than the code you are writing. I have written some vba code to do the same task using variant arrays. I am also using the With End With construct which also should speed up thing a little bit:
I have annotated to code to hel;p you understand what it does:

Sub test2()
With Worksheets("Labour")
lastrow = .Cells(Rows.Count, "D").End(xlUp).Row ' find the last row on labour sheet
datar = .Range(.Cells(1, 4), .Cells(lastrow, 16)) ' pick all the data in columns D to P  from row 1 to the last row
End With


With Worksheets("TskSheet")
inarr = .Range(.Cells(13, 4), .Cells(27, 4)) ' pick up the values to look up
outarr = .Range(.Cells(13, 8), .Cells(27, 8)) ' define the output array
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
   For j = 1 To lastrow        ' loop through each row of the Labour data to find a match
    If inarr(i, 1) = datar(j, 1) Then ' check for a match
     outarr(i, 1) = datar(j, 13)      ' copy data to output when matched
     Exit For                         ' exit the inner loop because we have matched the look up
    End If
   Next j
Next i
.Range(.Cells(13, 8), .Cells(27, 8)) = outarr ' write output array back to the workhseet
End With
End Sub

PS this also has the advantage you can deal with any "errors" yourself without causing a crash

 

[Marc L]

​

Yes, or just using directly formulas without looping …​

 

[bhalbach]

The way you are trying to use worksheet functions to do lookups in VBA is not a very efficient use of VBA. Accessing the worksheet from vba takes relatively long time. Accessing one single cell takes about as long as accessing a whole range, your code makes three accesses to worksheet on every loop. Since your loop in only a 14 rows this is probably not important, but if it was a few thousand rows your code would be very slow. You said you wanted the results returned quickly. Using variant array is usually about 1000 times faster than the code you are writing. I have written some vba code to do the same task using variant arrays. I am also using the With End With construct which also should speed up thing a little bit:
I have annotated to code to hel;p you understand what it does:

Sub test2()
With Worksheets("Labour")
lastrow = .Cells(Rows.Count, "D").End(xlUp).Row ' find the last row on labour sheet
datar = .Range(.Cells(1, 4), .Cells(lastrow, 16)) ' pick all the data in columns D to P  from row 1 to the last row
End With


With Worksheets("TskSheet")
inarr = .Range(.Cells(13, 4), .Cells(27, 4)) ' pick up the values to look up
outarr = .Range(.Cells(13, 8), .Cells(27, 8)) ' define the output array
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
   For j = 1 To lastrow        ' loop through each row of the Labour data to find a match
    If inarr(i, 1) = datar(j, 1) Then ' check for a match
     outarr(i, 1) = datar(j, 13)      ' copy data to output when matched
     Exit For                         ' exit the inner loop because we have matched the look up
    End If
   Next j
Next i
.Range(.Cells(13, 8), .Cells(27, 8)) = outarr ' write output array back to the workhseet
End With
End Sub

PS this also has the advantage you can deal with any "errors" yourself without causing a crash

This works awesome, I can not see any delay like I did with the code I was using. Thank you for your

I presume this code could also do a calculation at the same time?
While running the lookup and match can it multiply the resultant value that goes in column H x column E & F & G? So....the value that is found in column H = the returned value * E * F * G. Then I can eliminate a formula.

I really appreciate you help.

 

[bhalbach]

The way you are trying to use worksheet functions to do lookups in VBA is not a very efficient use of VBA. Accessing the worksheet from vba takes relatively long time. Accessing one single cell takes about as long as accessing a whole range, your code makes three accesses to worksheet on every loop. Since your loop in only a 14 rows this is probably not important, but if it was a few thousand rows your code would be very slow. You said you wanted the results returned quickly. Using variant array is usually about 1000 times faster than the code you are writing. I have written some vba code to do the same task using variant arrays. I am also using the With End With construct which also should speed up thing a little bit:
I have annotated to code to hel;p you understand what it does:

Sub test2()
With Worksheets("Labour")
lastrow = .Cells(Rows.Count, "D").End(xlUp).Row ' find the last row on labour sheet
datar = .Range(.Cells(1, 4), .Cells(lastrow, 16)) ' pick all the data in columns D to P  from row 1 to the last row
End With


With Worksheets("TskSheet")
inarr = .Range(.Cells(13, 4), .Cells(27, 4)) ' pick up the values to look up
outarr = .Range(.Cells(13, 8), .Cells(27, 8)) ' define the output array
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
   For j = 1 To lastrow        ' loop through each row of the Labour data to find a match
    If inarr(i, 1) = datar(j, 1) Then ' check for a match
     outarr(i, 1) = datar(j, 13)      ' copy data to output when matched
     Exit For                         ' exit the inner loop because we have matched the look up
    End If
   Next j
Next i
.Range(.Cells(13, 8), .Cells(27, 8)) = outarr ' write output array back to the workhseet
End With
End Sub

PS this also has the advantage you can deal with any "errors" yourself without causing a crash

Sorry I told you the calculations I needed wrong.

I need it to place the resultant just like you have it...then calculate that resultant *E*F*G and put this result in "I".

Could you assist me with the code on this last thing?

I would really appreciate it...I have 9 different macros that I need that are very similar to this one and I could modify your code then.