title: VBA excel, compare strings from two workbooks
body: <p>Once again I've got a problem with mixed format data in excel file. I've got the file with column named "User Name". The biggest problem for me to solve is that data in this column consists of last name and first name of person in totally mixed format. For example:</p>
  
  <blockquote>
    <p>"User Name"<br/>
    John Smith<br/>
    Carter Mike<br/>
    Garfield, Tom<br/></p>
  </blockquote>
  
  <p>And so on...</p>
  
  <p>I've got the second file which contains data in the proper format, I've got there 3 columns: "Last Name", "First Name" and "Id". What I need to do is to copy the Id that match the person from the second file to the first file. </p>
  
  <p>My idea was to loop through "User Name" column and compare it using InStr function with at first "Last Name" column and if it matches to compare with "First Name" column. Then if there is a match I would copy Id to the column "B" in the first file. But it turned out that I'm too weak in VBA.</p>
  
  <p>Here is what I've written. It doesn't work at all, but maybe will help you to understand what my problem is:</p>
  
  <pre><code>Option Explicit
  
  Sub FindID()
  
  Dim rLastCell, rFirstCell, rUserCell As Range
  Dim LastCell, FirstCell, UserCell As Range
  Dim file As Workbook
  Dim i As Long
  
  'open file to compare
  Set file = Workbooks.Open(Filename:=ThisWorkbook.Path &amp; "\gooddata.xls")
  
  'set last used cell
  Set rLastCell = file.Worksheets("Sheet1").Range("A65536").End(xlUp)
  Set rFirstCell = file.Worksheets("Sheet1").Range("B65536").End(xlUp)
  Set rUserCell = ThisWorkbook.Worksheets("Sheet1").Range("C65536").End(xlUp)
  
  With ThisWorkbook.Sheets("Sheet1")
  
  For Each UserCell In .Range("C2:C" &amp; rUserCell.Row)
      For Each LastCell In file.Sheets("Sheet1").Range("A2:A" &amp; rLastCell.Row)
          If InStr(0, UserCell.Value, LastCell.Value, vbTextCompare) &lt;&gt; 0 Then
              For Each FirstCell In file.Sheets("Sheet1").Range("B2:B" &amp; rFirstCell.Row)
                  If InStr(0, UserCell.Value, FirstCell.Value, vbTextCompare) &lt;&gt; 0 Then
                      'copy id from gooddata.xls to baddata.xls
                  End If
              Next FirstCell
          End If
      Next LastCell
  Next UserCell
  
  End With
  
  file.Close savechanges:=True
  Set file = Nothing
  
  End Sub
  </code></pre>
  
  <p>Thanks for any advice.</p>