0

I have a For loop that is trying to see if an item ID and quantity matches the item ID and quantity in another worksheet. If those criteria are met, then I want to return the value in one column to the cell this loop was being run on.

Variables: wsUS As Worksheet wsTHEM As Worksheet LastRowUS As Long

Assuming the variables are properly defined and set, here is my demo code:

'Starts at 2nd row due to header
For i = 2 To LastRowUS
   'First If matches Item IDs
   If wsUS.Range("F" & i) = wsTHEM.Range("E" & i) Then
      'Second If sees if other worksheet returned "Match item quantities
      If wsTHEM.Range("Y" & i) = "Match" Then
         'Return the value if column X in wsTHEM to column Y in wsUS if ID and quantities matched
         wsUS.Range("Y" & i).Value = wsTHEM.Range("X" & i).Value
      Else
         'Return text
         wsUS.Range("Y" & i).Value = "Discrepancy"
      End If
   End If
Next I

It seems this loop is working for some rows, but how with Index/Match or this loop can I return the value in Column X of wsTHEM if both criteria are met for ID & Quantity?

Community
  • 1
  • 1
HTMLame
  • 25
  • 6
  • 1
    Could you perhaps use a [multiple condition index/match formula](https://www.deskbright.com/excel/index-match-multiple-criteria/) instead of VBA? – BruceWayne Oct 31 '19 at 21:10
  • Here are other example on how to do this with a formula: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another – Scott Craner Oct 31 '19 at 21:24
  • The current problem is that you are looping the input range in sync with the lookup range. You will need a nested loop, the outer to loop the input range and the outer to loop the lookup range. But that will be slow. You really want to load everything into variant arrays and loop them. – Scott Craner Oct 31 '19 at 21:28

0 Answers0