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.

 

[offthelip]

Can I suggest that if you are going to master VBA you need to be much more careful about a number of things:
1: be clear at the start of what the full requirements are
2: Be Exact in how specify things , your last two posts have different requires, E? or E and F? . You never specified which worksheet you wanted the columns E , F and G to be used to multiply by H, I had to guess.
3: Look very carefully at all code, you failed to notice that I had posted an update to the code I had written. Look at the two bits of code and see what I have changed to add the E*F * G * H bit, IT isn't just one line that changed there were other changes as well
4: Try and understand how this code works.
Finally here is the update to delete cotents of E and F on the Tsksheet when the value in column D is blank

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, 7)) ' pick up the values to look up
outarr = .Range(.Cells(13, 8), .Cells(27, 9)) ' define the output array
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
   If inarr(i, 1) = "" Then
    inarr(i, 2) = "" ' this clears the value in column E
    inarr(i, 3) = "" ' this clears the value in column F , delete this line if you don't need it
   Else
    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
      outarr(i, 2) = inarr(i, 2) * inarr(i, 3) * inarr(i, 4) * outarr(i, 1) ' E * F * G * H
      Exit For                         ' exit the inner loop because we have matched the look up
     End If
    Next j
   End If
Next i
.Range(.Cells(13, 8), .Cells(27, 9)) = outarr ' write output array back to the workhseet
.Range(.Cells(13, 4), .Cells(27, 7)) = inarr   ' write column D to F back because we might have cleared some rows

End With
End Sub

 

[bhalbach]

Can I suggest that if you are going to master VBA you need to be much more careful about a number of things:
1: be clear at the start of what the full requirements are
2: Be Exact in how specify things , your last two posts have different requires, E? or E and F? . You never specified which worksheet you wanted the columns E , F and G to be used to multiply by H, I had to guess.
3: Look very carefully at all code, you failed to notice that I had posted an update to the code I had written. Look at the two bits of code and see what I have changed to add the E*F * G * H bit, IT isn't just one line that changed there were other changes as well
4: Try and understand how this code works.
Finally here is the update to delete cotents of E and F on the Tsksheet when the value in column D is blank

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, 7)) ' pick up the values to look up
outarr = .Range(.Cells(13, 8), .Cells(27, 9)) ' define the output array
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
   If inarr(i, 1) = "" Then
    inarr(i, 2) = "" ' this clears the value in column E
    inarr(i, 3) = "" ' this clears the value in column F , delete this line if you don't need it
   Else
    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
      outarr(i, 2) = inarr(i, 2) * inarr(i, 3) * inarr(i, 4) * outarr(i, 1) ' E * F * G * H
      Exit For                         ' exit the inner loop because we have matched the look up
     End If
    Next j
   End If
Next i
.Range(.Cells(13, 8), .Cells(27, 9)) = outarr ' write output array back to the workhseet
.Range(.Cells(13, 4), .Cells(27, 7)) = inarr   ' write column D to F back because we might have cleared some rows

End With
End Sub

Sorry I was

 

[bhalbach]

Sorry I was

Sorry I missed explaining so poorly. I made incorrect statements...forgot there were no formulas left because of all the code.

It works exactly as I expected....except I should have have said H & I cleared as well if D is empty, all on Tsksheet.

Can I just add to the code for H & I? Same as you coded for E & F? I reading through your code trying to understand if there is another location to change or just the followin spot.
inarr(i, 2) = "" ' this clears the value in column E
inarr(i, 3) = "" ' this clears the value in column F

like this...
inarr(i, 5) = "" ' this clears the value in column H ??
inarr(i, 6) = "" ' this clears the value in column I ??

I cant thank you enough...this code will help me understand.

 

[offthelip]

like this...
inarr(i, 5) = "" ' this clears the value in column H ??
inarr(i, 6) = "" ' this clears the value in column I ??

Brilliant that is the way to do it!!

 

[bhalbach]

Brilliant that is the way to do it!!

I tried that and I get an error...I don’t understand why it won’t accept that simple change.

 

[offthelip]

Sorry Ignore that last comment, I wrote it too quickly the real answer is this one:
H and I are the original columns that you wanted values output . It was only later that you added the requirement to clear E and F, The orginal solution defined an input array (Inarr) that picked up column D. so my solution rather than starting again, was extend the array that was orginally just column D to include columns E to F. I then cleared these in the array and wrote the array back, which wasn't orginally in the code. The outputs H and I weren't changed by this. So the way to clear columns H and I is clear the output array outarr

     inarr(i, 2) = "" ' this clears the value in column E
     inarr(i, 3) = "" ' this clears the value in column F
     outarr(i, 1) = ""
     outarr(i, 2) = ""

What all of this demonstrates is that if you keep changing the requirements of a bit of code you end up with code that is messy and not consistent, because bits have been changed from their original design.
For the first iteration of the code Inarr was just used for Inputs and outarr was just used for outputs , now it is not quite as simple as that.
However it should work!!

 

[bhalbach]

Sorry Ignore that last comment, I wrote it too quickly the real answer is this one:
H and I are the original columns that you wanted values output . It was only later that you added the requirement to clear E and F, The orginal solution defined an input array (Inarr) that picked up column D. so my solution rather than starting again, was extend the array that was orginally just column D to include columns E to F. I then cleared these in the array and wrote the array back, which wasn't orginally in the code. The outputs H and I weren't changed by this. So the way to clear columns H and I is clear the output array outarr

     inarr(i, 2) = "" ' this clears the value in column E
     inarr(i, 3) = "" ' this clears the value in column F
     outarr(i, 1) = ""
     outarr(i, 2) = ""

What all of this demonstrates is that if you keep changing the requirements of a bit of code you end up with code that is messy and not consistent, because bits have been changed from their original design.
For the first iteration of the code Inarr was just used for Inputs and outarr was just used for outputs , now it is not quite as simple as that.
However it should work!!

Thank you for the explanation...I am going to try and write this out from scratch taking all the information you provided and see if I can produce a code as you explain to get to this final results.

thank you so much for taking the time and explaining, it’s been a learning experience.

 

[offthelip]

I am pleased to be able to help,

 

[bhalbach]

Sorry Ignore that last comment, I wrote it too quickly the real answer is this one:
H and I are the original columns that you wanted values output . It was only later that you added the requirement to clear E and F, The orginal solution defined an input array (Inarr) that picked up column D. so my solution rather than starting again, was extend the array that was orginally just column D to include columns E to F. I then cleared these in the array and wrote the array back, which wasn't orginally in the code. The outputs H and I weren't changed by this. So the way to clear columns H and I is clear the output array outarr

     inarr(i, 2) = "" ' this clears the value in column E
     inarr(i, 3) = "" ' this clears the value in column F
     outarr(i, 1) = ""
     outarr(i, 2) = ""

What all of this demonstrates is that if you keep changing the requirements of a bit of code you end up with code that is messy and not consistent, because bits have been changed from their original design.
For the first iteration of the code Inarr was just used for Inputs and outarr was just used for outputs , now it is not quite as simple as that.
However it should work!!

I am trying to make sense of this code you provided...it works great by the way.

I am still a bit confused on a couple elements...I have commented on the code below where I am struggling to make sense of the code.

It is in the inarr and the outarr that i dont understand, could you explain that?



Sub test5() ' works perfect
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, 7)) ' pick up the values to look up ' WHAT IS THE 7?
outarr = .Range(.Cells(13, 8), .Cells(27, 9)) ' define the output array ' WHAT IS TH 9?
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
If inarr(i, 1) = "" Then
inarr(i, 2) = "" ' this clears the value in column E
inarr(i, 3) = "" ' this clears the value in column F , delete this line if you don't need it
outarr(i, 1) = "" ' this addition clears H & I
outarr(i, 2) = "" ' this addition clears H & I


Else
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
outarr(i, 2) = inarr(i, 2) * inarr(i, 3) * inarr(i, 4) * outarr(i, 1) ' E * F * G * H
Exit For ' exit the inner loop because we have matched the look up
End If
Next j
End If
Next i
.Range(.Cells(13, 8), .Cells(27, 9)) = outarr ' write output array back to the workhseet ' WHAT IS TH 9?
.Range(.Cells(13, 4), .Cells(27, 7)) = inarr ' write column D to F back because we might have cleared some rows ' WHAT IS THE 7?

End With
End Sub

 

[offthelip]

 .Range(.Cells(13, 4), .Cells(27, 7))

This is a method of addressing the worksheet which uses number for the rows and the columns. So cells(13,4) is the 13th row and the 4th column: ABCD thus this cell is row13 column 4 i.e D13.
Cells (27,7) is the cells that marks row 27 and column 7, ABCDEFG i.e G27, so this statement is exactly equivalent to:

 .Range("D13:G27")

The main reason for using numbers instead of letters is that once you have loaded the worksheet into a variant array YOU HAVE to use numbers. So the addressing for inarr and outarr has to be using numbers. The array numbers start at the position the array is loaded from so the first elements of the inarr array is the value in D13, so
inarr (1,1) = cells("d13").value
inarr (2,1) =cells("D14").value
inarr(2,2)=cells("E14").value
etc , etc down to
inarr(inarr(15,4)= cells("G27").value
If you load an array starting at cell A1, then the numbers in the array tie up with the row and column numbers, i.e.
if we had loaded inarr like this:
inarr=range(cells(1,1),cells(27,7)) the array would have been larger and might have been a bit slower to load, but it would simpilfy the addressing
Eg. cells("d13").value=inarr(13,4)
So I try to do this if I know there is nothing in the extra areas I load into the array which could be destroyed when I write it back. i.e. a formula