title: Vba Lookup value on sheet x if value like or similar to?
body: <p>I have a workbook with 2 sheets.</p>
  
  <p>Sheet1:</p>
  
  <pre><code>Column B               Column C     Column D      Column E
  
  Dairy Crest Ltd        
  Milk Farm
  Tuna Family
  Guiness
  </code></pre>
  
  <p>Sheet 2:</p>
  
  <pre><code>Column A                   Column B     Column C           Column d   
  Dairy Crest                James        james@email.com    07874565656
  Milk Farm Limited          Kelly        kely@email.com     07874565656
  Tuna's Families            Dave         dave@email.com     07874565656
  Guiness Prep Limited       Tom          tom@email.com      07874565656
  </code></pre>
  
  <p>I want to match the similar named companies. This can't be a case of saying if value = value because the company name is usually spelt different.</p>
  
  <p>Instead i want to use like or wildcard. Would this work?</p>
  
  <p>If i use Value Like Value this doesn't seem to work.</p>
  
  <p>Where found, i want to copy the contact name, email and contact number over to sheet 1 in the relevant columns.</p>
  
  <p>For some reason this is not working. Please can someone show me where i am going wrong?</p>
  
  <p>Relevant code:</p>
  
  <pre><code>  'Start second loop sequence
                  With ThisWorkbook.Worksheets(3)
                 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                 j2 = 2
                 For i2 = 1 To LastRow
  
                 ' === For DEBUG ONLY ===
                 Debug.Print ThisWorkbook.Worksheets(2).Range("B" &amp; j2).Value
  
                 If ThisWorkbook.Worksheets(2).Range("B" &amp; j2).Value = .Range("A" &amp; i2).Value Then      ' check if Week No equals the value in "A1"
  
                  ThisWorkbook.Worksheets(2).Range("C" &amp; j2).Value = .Range("B" &amp; i2).Value
                  ThisWorkbook.Worksheets(2).Range("D" &amp; j2).Value = .Range("D" &amp; i2).Value
                  ThisWorkbook.Worksheets(2).Range("E" &amp; j2).Value = .Range("C" &amp; i2).Value
  
                  j2 = j2 + 1
  
                  End If
                  Next i2
                  End With
  
                  'End Second Loop
  </code></pre>
  
  <p>Full COde:</p>
  
  <pre><code>Option Explicit
  
  Sub LoadWeekAnnouncementsFromPlanner()
  
  Dim WB As Workbook
  Dim WB2 As Workbook
  Dim i As Long
  Dim i2 As Long
  Dim j As Long
  Dim j2 As Long
  Dim LastRow As Long
  Dim ws As Worksheet
  
  'Open Planner
  'On Error Resume Next
  Set WB = Workbooks("2017 Planner.xlsx")
  On Error GoTo 0
  If WB Is Nothing Then 'open workbook if not open
      Set WB = Workbooks.Open("G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\8. 2017\2017 Planner.xlsx", xlUpdateLinksNever, True, Password:="samples")
  End If
  
  'Open PhoneBook
  'On Error Resume Next
  'On Error GoTo 0
  
  ' ======= Edit #2 , also for DEBUG ======
  With WB.Worksheets(1)
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
      j = 2
  
      For i = 1 To LastRow
  
  
          ' === For DEBUG ONLY ===
          Debug.Print CInt(ThisWorkbook.Worksheets(1).Range("I8").Value)
  
  
          If CInt(ThisWorkbook.Worksheets(1).Range("I8").Value) = .Range("A" &amp; i).Value Then   ' check if Week No equals the value in "A1"
  
                  ThisWorkbook.Worksheets(2).Range("A" &amp; j).Value = .Range("A" &amp; i).Value
                  ThisWorkbook.Worksheets(2).Range("B" &amp; j).Value = .Range("N" &amp; i).Value
                  ThisWorkbook.Worksheets(2).Range("H" &amp; j).Value = .Range("K" &amp; i).Value
                  ThisWorkbook.Worksheets(2).Range("I" &amp; j).Value = .Range("L" &amp; i).Value
  
                  ThisWorkbook.Worksheets(2).Range("J" &amp; j).Value = .Range("M" &amp; i).Value
                  ThisWorkbook.Worksheets(2).Range("K" &amp; j).Value = .Range("G" &amp; i).Value
  
                  ThisWorkbook.Worksheets(2).Range("L" &amp; j).Value = .Range("O" &amp; i).Value
                  ThisWorkbook.Worksheets(2).Range("M" &amp; j).Value = .Range("P" &amp; i).Value
  
                  ThisWorkbook.Worksheets(2).Range("N" &amp; j).Value = .Range("W" &amp; i).Value
                  ThisWorkbook.Worksheets(2).Range("O" &amp; j).Value = .Range("Z" &amp; i).Value
  
  
  
  
                   'Start second loop sequence
                  With ThisWorkbook.Worksheets(3)
                 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                 j2 = 2
                 For i2 = 1 To LastRow
  
                 ' === For DEBUG ONLY ===
                 Debug.Print ThisWorkbook.Worksheets(2).Range("B" &amp; j2).Value
  
                 If ThisWorkbook.Worksheets(2).Range("B" &amp; j2).Value = .Range("A" &amp; i2).Value Then      ' check if Week No equals the value in "A1"
  
                  ThisWorkbook.Worksheets(2).Range("C" &amp; j2).Value = .Range("B" &amp; i2).Value
                  ThisWorkbook.Worksheets(2).Range("D" &amp; j2).Value = .Range("D" &amp; i2).Value
                  ThisWorkbook.Worksheets(2).Range("E" &amp; j2).Value = .Range("C" &amp; i2).Value
  
                  j2 = j2 + 1
  
                  End If
                  Next i2
                  End With
  
                  'End Second Loop
  
  
                 j = j + 1
  
                 End If
                 Next i
                 End With
  
  End Sub
  </code></pre>
  
  <p>Please can someone show me where i am going wrong?</p>