title: Excel VBA updating cells with Find/Replace from a different sheet
body: <p>Hey i have a sheet1 containing a list of search patterns in column A, and a corresponding list of category names in column B. I have a sheet2 which has a list of various descriptions of my bank transactions.</p>
  
  <p>Eg in sheet1 i have groceries, fuel, entertainment, savings, and in sheet2 i have "Shell service station, abc road", "Coles supermarket" etc..</p>
  
  <blockquote>
    <blockquote>
      <p><b>I want to find words in the transactions columns, and then replace the found line with a category..</b></p>
    </blockquote>
  </blockquote>
  
  <p>Eg.  If i find the word "Shell" in Sheet2 i want to replace that line with the word "Fuel"</p>
  
  <p>So far i have got this working, but i dont believe that it is the most efficient or effective way of doing it.  Below is my code.</p>
  
  <pre><code>Sub UpdateCats()
  Dim x As Integer
  Dim FindString As String
  Dim ReplaceString As String
  Dim NumRows As Integer
  'Replace and update Categories
  With Sheets("Categories")
      .Activate
    ' Set numrows = number of rows of data.
    NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
    ' Select cell a1.
    Range("A2").Select
    ' Establish "For" loop to loop "numrows" number of times.
    For x = 1 To NumRows
       FindString = ActiveCell.Value
       ActiveCell.Offset(0, 1).Select
       ReplaceString = ActiveCell.Value
       ActiveCell.Offset(1, -1).Select
  
       With Sheets("Data")
          .Activate
          'With Columns(2)
          Cells.Replace What:=FindString, Replacement:=ReplaceString, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
          'End With
       End With
       Sheets("Categories").Activate
    Next
  End With
  End Sub
  </code></pre>
  
  <p>The reason i dont like my code so far is because it has to continually switching (activating) between sheets as it runs through the loop.  Is there a way to do this better?</p>