title: Find Matches in Column and Replace from External File
body: <p>I use this VBA code which works very well. It searches in column A of an external Excel file for all the terms in column D, and replaces the matches, with the content of column B of the external file in the found row.  So for instance:</p>
  <p><em>if D5 matches A11 of the external file, then B11 from external file is written to D5.</em></p>
  <p>I am now trying to modify it so that it still searches in column 4 for matches in column A of external file, but for any matches found, replaces the column E with column B of the external file. So:</p>
  <p><em>If D5 matches A11, then B11 from external file is written to E5.</em></p>
  <p>Well, I've tried many changes in the replace loop but it throws errors every time. I suppose I don't use the correct command!</p>
  <pre><code>Private Sub CommandButton1_Click()
  Dim NameListWB As Workbook, thisWb As Workbook
      Dim NameListWS As Worksheet, thisWs As Worksheet
      Dim i As Long, lRow As Long
  
      'This is the workbook from where code runs
      Set thisWb = ThisWorkbook
      Set thisWs = thisWb.Sheets(&quot;Sheet1&quot;)
  
      'External file
      Set NameListWB = Workbooks.Open(&quot;E:\Data.xlsx&quot;)
      Set NameListWS = NameListWB.Worksheets(&quot;Sheet1&quot;)
  
      With NameListWS
          'Detect end row in Col A of Data.xlsx
          lRow = .Range(&quot;A&quot; &amp; .Rows.Count).End(xlUp).Row
  
          'Loop though Column A
          For i = 1 To lRow
              '... and perform replace action
              thisWs.Columns(4).Replace What:=.Range(&quot;A&quot; &amp; i).Value, _
                                        Replacement:=.Range(&quot;B&quot; &amp; i).Value, _
                                        SearchOrder:=xlByColumns, _
                                        MatchCase:=False
          Next i
      End With
  End Sub ```
  </code></pre>