title: Filter and Replace in Different Column
body: <p>I have some data in the following format:</p>
  
  <p>Table 1, 65,000 rows:</p>
  
  <pre><code>Food          Type
  Mango         Fruit
  Mango         Fruit
  Mango         Vegetable
  Pineapple     Fruit
  Cabbage       Flower
  Carrot        Fruit
  </code></pre>
  
  <p>etc, with many columns, where the "Type" column might be incorrect.</p>
  
  <p>Therefore, I would like to correct the "Type" column from another different data set which has correct type mappings:</p>
  
  <p>Table 2, 3000 rows:</p>
  
  <pre><code>Food      Type
  Cabbage   Vegetable
  Carrot    Vegetable
  Mango     Fruit
  </code></pre>
  
  <p>etc.</p>
  
  <p>I am trying to update the "Type" data in Table 1 with the "Type" from Table 2.</p>
  
  <p>Normally, in MATLAB or R, I would use vector notations which would be quite fast. Manually, I would use VLOOKUP() which is also not slow, but I would like to remove this manual process.</p>
  
  <p>I can't seem to get it to be fast in VBA for Excel. Double loops are obviously not an option, so I thought I'd use the following approach:</p>
  
  <pre><code>Worksheets(Table1).Activate
  For index = 2 To lastType    'For loop that goes from row 2 to last row of Table2
  
  
      'Filter for the rows we need    
      With Range("A1")
          .AutoFilter Field:=1, Criteria1:=Worksheets(Table2).Cells(index, 1).Value
      End With
  
  
      'Update the value of column 2, Table1 with the corresponding value from column 2, Table2
      Range("A2:A" &amp; lastData).SpecialCells(xlCellTypeVisible).Offset(0, 1).Value = Worksheets(Table2).Cells(index, 2).Value
  
      ActiveSheet.AutoFilterMode = False
  Next index
  </code></pre>
  
  <p>Any more efficient approach suggestions would be appreciated.</p>