I'm trying to make an userform with a textbox. The value entered in the textbox is the search value for VLOOKUP.
The VLOOKUP must search for the value in two sheets and give its result in a msgbox.

I've been only able to get Err.Number = 1004 as reply.

What am I doing wrong?


Private Sub CommandButton1_Click()
On Error GoTo Myerrorhandler

Dim Batch As String
Dim Result As Single
Dim sheetsArray As Sheets
Set sheetsArray = ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2"))

Dim Target As Range
Dim sheetObject As Worksheet

Batch = TextBox1.text

For Each sheetObject In sheetsArray
Set Target = sheetObject.Range("C7:ZZ1000")

Result = TextBox1.text
Result = "Batch: " & Application.WorksheetFunction.VLookup(Batch, Target, 5, False)

Next sheetObject

MsgBox "Batch details:" & vbNewLine & Result

Exit Sub

Myerrorhandler:
If Err.Number = 1004 Then
MsgBox "Not present"

ElseIf Err.Number = 13 Then
MsgBox "Invalid value"
End If
End Sub