title: Excel/VBA Comparing two columns and copying the cell contents from a third column into a fourth column in two different sheets
body: <p>I have to write a macro in Excel for a co-worker. It should import .xlsx files and then compare first and last names between two columns in two different sheets. If a match has been found in the second sheet, the cell contents of another column in the second sheet have to be copied and pasted into a column in the first sheet. The macro to import the files is done and works perfectly. Due to me being very new to Macros/VBA, I'm experiencing some difficulties in comparing between the sheets and copying.
  <br>Example:  </p>
  
  <p><strong>Sheet 1</strong></p>
  
  <p><strong><em>Column A</em></strong> (First and Last Name)</p>
  
  <ul>
  <li>John Doe </li>
  <li>Luigi Ditalia </li>
  <li>Mario Ditalia</li>
  </ul>
  
  <p><em>Column N</em> (DEPARTEMENTS)<br></p>
  
  <p><strong>Sheet 2</strong></p>
  
  <p><strong><em>Column B</em></strong> (DEPARTEMENTS)<br></p>
  
  <ul>
  <li>Finances</li>
  <li>Administration</li>
  <li>Helpdesk</li>
  </ul>
  
  <p><strong><em>Column C</em></strong> (First and Last Name)</p>
  
  <ul>
  <li>John Doe</li>
  <li>Luigi Ditalia</li>
  <li>Mario Ditalia</li>
  </ul>
  
  <p>As you can see, the user "<em>John Doe</em>" is present in Column A in sheet 1 AND in Column C in sheet 2. Now I need to copy the cell contents of Column B (for the same person) to the Column N in the same row as "John Doe". How can I do this?</p>
  
  <p>This is what I have so far:</p>
  
  <pre><code>Sub compareCols()
      Dim masterWorkbook As Workbook
      Set masterWorkbook = Application.ActiveWorkbook
  
      Dim masterSheet_caption As String
      Dim masterSheet_msg As String
      Dim masterSheet_Name As String
      masterSheet_caption = "Choosing a mastersheet"
      masterSheet_msg = "In which sheet should the departement of the users be written in if a match has been found in the imported sheet? (this will be the mastersheet)"
      masterSheet_Name = InputBox(masterSheet_msg, masterSheet_caption, User)
  
      Dim masterSheet_columnFL_msg As String
      Dim masterSheet_columnFL_FirstCell As String
      masterSheet_columnFL_msg = "Which is the first cell (in the mastersheet) in which the first and the last names of the users can be found?"
      masterSheet_columnFL_FirstCell = InputBox(masterSheet_columnFL_msg, masterSheet_caption, User)
  
      Dim importedSheet_caption As String
      Dim importedSheet_msg As String
      Dim importedSheet_Name As String
      importedSheet_caption = "Choosing the imported sheet"
      importedSheet_msg = "What sheet has been imported to check for duplicates in the columns containing first and last names? (default: import)"
      importedSheet_Name = InputBox(importedSheet_msg, importedSheet_caption, User)
  
      Dim masterSheet As Worksheet
      Dim importedSheet As Worksheet
      Set masterSheet = masterWorkbook.Worksheets(masterSheet_Name)
      Set importedSheet = masterWorkbook.Worksheets(importedSheet_Name)
  
  End Sub
  </code></pre>
  
  <p><strong>FOLLOWUP</strong></p>
  
  <p>At least VBA is English only. Therefore I'm going to try that. This should do the trick:</p>
  
  <pre><code>Sub test()
  Dim masterWorkbook As Workbook
  Set masterWorkbook = Application.ActiveWorkbook
  
  Dim masterSheet As Worksheet
  Dim importedSheet As Worksheet
  Set masterSheet = masterWorkbook.Worksheets("Online")
  Set importedSheet = masterWorkbook.Worksheets("import")
  
  masterSheet.Range("N1:N871").Formula = _
  "=INDIRECT(""importedSheet!B"" & INDEX(MATCH(A1,importedSheet!C:C,0),0,0))"
  
  masterSheet.Range("N1:N871").Value = masterSheet.Range("N1:N871").Value
  End Sub</code></pre>
  
  <p>All I get with this is 0s in the column N in sheet 1 (masterSheet). But at least it is something! Now I'll just need to find the "error". And I guess it's the traduction. Give me 5 minutes. </p>