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
Bookmarks