VBA Vlookup

[Joseph111]

Please for help. Why it`s not works?

Sub Check_equpment1_click()
Worksheets("Production_program").Activate
Dim ans1 As Integer
ans1 = MsgBox("Ñìÿíà íà àñîðòèìåíò ïî äàòà/Check Job change by Date", vbQuestion + vbYesNo + vbDefaultButton2, "Equipment")
If ans1 = vbYes Then
On Error GoTo MyErrorHandler1:
Dim Date1 As String
Dim Range1 As Range
Date1 = InputBox("Âúâåäè äàòà/Enter date:", Title:="Equipment by date")
For Each Range1 In Sheet1.Range("B13:B500")
If Date1 = Range1.Value Then
Det = "Êîä íà Àñîðòèìåíòà (Article code):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 7, False)
Det = Det & vbNewLine & "Èìå íà Àñîðòèìåíòà (Article name):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 8, False)
Det = Det & vbNewLine & "Ëèíèÿ (Line):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 9, False)
Det = Det & vbNewLine & "Ìàøèíà (Machine):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 10, False)
Det = Det & vbNewLine & "Äîëíè Çâåçäè (Lower starwheels): " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 11, False)
Det = Det & vbNewLine & "Ìÿñòî (Place):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 12, False)
Det = Det & vbNewLine & "Ãîðíè Çâåçäè (Upper starwheels):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 13, False)
Det = Det & vbNewLine & "Ìÿñòî (Place):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 14, False)
Det = Det & vbNewLine & "Øíåê (Infeed screw):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 15, False)
Det = Det & vbNewLine & "Êàëèáúð (Plug gauge):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 16, False)
Det = Det & vbNewLine & "Èçõîäÿùè ðåëñè - äîëíè (outfeed guides lower):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 17, False)
Det = Det & vbNewLine & "Ìÿñòî (Place):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 18, False)
Det = Det & vbNewLine & "Èçõîäÿùè ðåëñè - ãîðíè (outfeed guides upper):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 19, False)
Det = Det & vbNewLine & "Ìÿñòî (Place):  " & Application.WorksheetFunction.VLookup(Date1, Sheet1.Range("B13:U500"), 20, False)
MsgBox "" & vbNewLine & Det
Exit For
End If
Next
MyErrorHandler1:
If Err.Number = 1004 Then
End If
End If
End Sub

 

[aRandomHelper]

Just to be sure, are you trying to find the date where some columns have "#N/A" showing in the cells? I suspect that might be the case.

Try this code:

Sub Check_equpment1_click()
    Worksheets("Production_program").Activate
    Dim ans1 As Integer
    ans1 = MsgBox("Ñìÿíà íà àñîðòèìåíò ïî äàòà/Check Job change by Date", vbQuestion + vbYesNo + vbDefaultButton2, "Equipment")
    If ans1 = vbYes Then
        On Error GoTo MyErrorHandler1:
        Dim Date1 As String
        Dim Range1 As Range
        Date1 = InputBox("Âúâåäè äàòà/Enter date:", Title:="Equipment by date")
        Set Range1 = Worksheets("Production_program").Range("Z13:Z500").Find(Date1, LookIn:=xlValues, lookat:=xlWhole)
        If Not Range1 Is Nothing Then
            Det = "Êîä íà Àñîðòèìåíòà (Article code): " & Cells(Range1.Row, "H").Text
            Det = Det & vbNewLine & "Èìå íà Àñîðòèìåíòà (Article name): " & Cells(Range1.Row, "I").Text
            Det = Det & vbNewLine & "Ëèíèÿ (Line): " & Cells(Range1.Row, "J").Text
            Det = Det & vbNewLine & "Ìàøèíà (Machine): " & Cells(Range1.Row, "K").Text
            Det = Det & vbNewLine & "Äîëíè Çâåçäè (Lower starwheels): " & Cells(Range1.Row, "L").Text
            Det = Det & vbNewLine & "Ìÿñòî (Place): " & Cells(Range1.Row, "M").Text
            Det = Det & vbNewLine & "Ãîðíè Çâåçäè (Upper starwheels): " & Cells(Range1.Row, "N").Text
            Det = Det & vbNewLine & "Ìÿñòî (Place): " & Cells(Range1.Row, "O").Text
            Det = Det & vbNewLine & "Øíåê (Infeed screw): " & Cells(Range1.Row, "P").Text
            Det = Det & vbNewLine & "Êàëèáúð (Plug gauge): " & Cells(Range1.Row, "Q").Text
            Det = Det & vbNewLine & "Èçõîäÿùè ðåëñè - äîëíè (outfeed guides lower): " & Cells(Range1.Row, "R").Text
            Det = Det & vbNewLine & "Ìÿñòî (Place): " & Cells(Range1.Row, "S").Text
            Det = Det & vbNewLine & "Èçõîäÿùè ðåëñè - ãîðíè (outfeed guides upper): " & Cells(Range1.Row, "T").Text
            Det = Det & vbNewLine & "Ìÿñòî (Place): " & Cells(Range1.Row, "U").Text
            MsgBox "" & vbNewLine & Det
        End If
MyErrorHandler1:
        If Err.Number = 1004 Then
        End If
    End If
End Sub

It will extract even any #N/A in text format, so the lines of codes extracting the info wouldn't trigger your error handler statement.

 

[Joseph111]

Just to be sure, are you trying to find the date where some columns have "#N/A" showing in the cells? I suspect that might be the case.

Try this code:

Sub Check_equpment1_click()
    Worksheets("Production_program").Activate
    Dim ans1 As Integer
    ans1 = MsgBox("Ñìÿíà íà àñîðòèìåíò ïî äàòà/Check Job change by Date", vbQuestion + vbYesNo + vbDefaultButton2, "Equipment")
    If ans1 = vbYes Then
        On Error GoTo MyErrorHandler1:
        Dim Date1 As String
        Dim Range1 As Range
        Date1 = InputBox("Âúâåäè äàòà/Enter date:", Title:="Equipment by date")
        Set Range1 = Worksheets("Production_program").Range("Z13:Z500").Find(Date1, LookIn:=xlValues, lookat:=xlWhole)
        If Not Range1 Is Nothing Then
            Det = "Êîä íà Àñîðòèìåíòà (Article code): " & Cells(Range1.Row, "H").Text
            Det = Det & vbNewLine & "Èìå íà Àñîðòèìåíòà (Article name): " & Cells(Range1.Row, "I").Text
            Det = Det & vbNewLine & "Ëèíèÿ (Line): " & Cells(Range1.Row, "J").Text
            Det = Det & vbNewLine & "Ìàøèíà (Machine): " & Cells(Range1.Row, "K").Text
            Det = Det & vbNewLine & "Äîëíè Çâåçäè (Lower starwheels): " & Cells(Range1.Row, "L").Text
            Det = Det & vbNewLine & "Ìÿñòî (Place): " & Cells(Range1.Row, "M").Text
            Det = Det & vbNewLine & "Ãîðíè Çâåçäè (Upper starwheels): " & Cells(Range1.Row, "N").Text
            Det = Det & vbNewLine & "Ìÿñòî (Place): " & Cells(Range1.Row, "O").Text
            Det = Det & vbNewLine & "Øíåê (Infeed screw): " & Cells(Range1.Row, "P").Text
            Det = Det & vbNewLine & "Êàëèáúð (Plug gauge): " & Cells(Range1.Row, "Q").Text
            Det = Det & vbNewLine & "Èçõîäÿùè ðåëñè - äîëíè (outfeed guides lower): " & Cells(Range1.Row, "R").Text
            Det = Det & vbNewLine & "Ìÿñòî (Place): " & Cells(Range1.Row, "S").Text
            Det = Det & vbNewLine & "Èçõîäÿùè ðåëñè - ãîðíè (outfeed guides upper): " & Cells(Range1.Row, "T").Text
            Det = Det & vbNewLine & "Ìÿñòî (Place): " & Cells(Range1.Row, "U").Text
            MsgBox "" & vbNewLine & Det
        End If
MyErrorHandler1:
        If Err.Number = 1004 Then
        End If
    End If
End Sub

It will extract even any #N/A in text format, so the lines of codes extracting the info wouldn't trigger your error handler statement.

For me it`s stange I tried everything but it doesn`t works, is it possible to be from the excell version, 365 is mine?

If you have a possibility can we make short video meeting to show you my screen? if you agree

 

[aRandomHelper]

Can you try commenting off the error handling, and see if Excel is throwing any errors? and if yes, what is it?

On Error GoTo MyErrorHandler1:

 

[Joseph111]

There isn`t any error when I switched MyErrorHandler1: off.

Just when I inputted the date nothing happened

 

[Joseph111]

Run time error '424'

Object required

 

[Joseph111]

Can you try commenting off the error handling, and see if Excel is throwing any errors? and if yes, what is it?

On Error GoTo MyErrorHandler1:

Run time error '424'

Object required

 

[aRandomHelper]

Object required

Click on Debug, and which line is highlighted yellow?