title: VLookup in multiple sheets
body: <p>I'm trying to create a userform, where the users can enter a batch number and when they hit OK a VLOOKUP function will give them the information they need. However, the target data is on two different sheets (e.g., sheet1 and sheet2). </p>
  
  <p>I've managed to create the VBA below, but it only works for sheet1. And if a wrong batch is entered, no result or msgbox is shown.</p>
  
  <p>What am I doing wrong?</p>
  
  <pre><code>  Private Sub CommandButton1_Click()
  On Error GoTo myerrorhandler
  
  Dim Batch As Long
  Dim Result
  Dim Result2
  Dim Target1 As Range
  Dim Target2 As Range
  
  Dim ws As Worksheet
  
  Batch = TextBox1.text
  
  Set Target1 = ActiveWorkbook.Sheets("sheet1").Range("C7:ZZ10000")
  Set Target2 = ActiveWorkbook.Sheets("sheet2").Range("C7:ZZ1000")
  
  
  Result = vbNewLine &amp; "Batch: " &amp; Application.WorksheetFunction.VLookup(Batch, Target1, 1, False)
  Result = Result &amp; vbNewLine &amp; "Batch: " &amp; Application.WorksheetFunction.VLookup(Batch, Target1, 17, False)
  Result = Result &amp; vbNewLine &amp; "Batch: " &amp; Application.WorksheetFunction.VLookup(Batch, Target1, 18, False)
  Result = Result &amp; vbNewLine &amp; "Batch: " &amp; Application.WorksheetFunction.VLookup(Batch, Target1, 20, False)
  
  If Result &gt; 0 Then
  MsgBox "Batch details:" &amp; vbNewLine &amp; Result
  
  ElseIf Result = 0 Then
  
  Result2 = vbNewLine &amp; "Batch: " &amp; Application.WorksheetFunction.VLookup(Batch, Target2, 1, False)
  Result2 = Result &amp; vbNewLine &amp; "Batch: " &amp; Application.WorksheetFunction.VLookup(Batch, Target2, 17, False)
  Result2 = Result &amp; vbNewLine &amp; "Batch: " &amp; Application.WorksheetFunction.VLookup(Batch, Target2, 18, False)
  Result2 = Result &amp; vbNewLine &amp; "Batch: " &amp; Application.WorksheetFunction.VLookup(Batch, Target2, 20, False)
  
  If Result &gt; 0 Then
  MsgBox "Batch details:" &amp; vbNewLine &amp; Result2
  
  ElseIf Result2 = 0 Then
  MsgBox "Not present, or wrong entry"
  
  
  End If
  End If
  Exit Sub
  
  
  myerrorhandler:
  If Err.Number = 13 Then
  MsgBox "Invalid value"
  End If
  End Sub
  </code></pre>