How to add "if no match found, msgbox: no match found." in this type of code

[Splifford1]

This code gives me a report.. but when the product selected in the combobox isn't found it gives me an error, I want it to just end the code and let me make a new search instead of the error.

In the code I posted below, where it says:
s = Sheets("Menu").ComboBox1
If s = "" Then Exit Sub

X = Sheets("BDInventario").ListObjects(1).DataBodyRange
For i = 1 To UBound(X, 1)
If X(i, 1) = s Then

...
What should I add onto this code to achieve this? I'm tired

Sub IndividualProductReportl()Application.ScreenUpdating = FalseSheet1.Unprotect Password:="...."
    Dim X, Y(), i As Long, iCnt As Integer, iSecStk As Integer, dCurStk As Double, s As String        s = Sheets("Menu").ComboBox1    If s = "" Then Exit Sub        X = Sheets("DBProducts").ListObjects(1).DataBodyRange    For i = 1 To UBound(X, 1)        If X(i, 1) = s Then            iCnt = iCnt + 1: ReDim Preserve Y(1 To 10, 1 To iCnt)            Y(1, iCnt) = X(i, 4): Y(2, iCnt) = X(i, 15): Y(3, iCnt) = X(i, 5): Y(4, iCnt) = X(i, 3)            Y(5, iCnt) = X(i, 8): Y(6, iCnt) = X(i, 9): Y(7, iCnt) = X(i, 10): Y(8, iCnt) = X(i, 6)            Y(9, iCnt) = X(i, 7): iSecStk = X(i, 12): dCurStk = dCurStk + X(i, 5)
        End If    Next    Application.ScreenUpdating = 0    With Sheets("ProductReport")        .Rows(4).Resize(10000).Delete        .[a4].Resize(iCnt, 9) = Application.Transpose(Y)        With .UsedRange.Offset(3).Resize(.UsedRange.Rows.Count - 3)            .Columns(2).NumberFormat = "$#,##0.000"            .Columns(4).NumberFormat = "$#,##0.000"            .Columns(6).NumberFormat = "[$-F800]dddd, mmmm dd, aaaa"            .Columns(7).NumberFormat = "[$-80A]hh:mm:ss AM/PM;@"            .Borders.Weight = 2        End With        .[D1] = s: .[h2] = iSecStk: .[h1] = dCurStk        .Activate        Application.Goto .[a1]    End With    Sheets("Menu").ComboBox1.ListIndex = -1

            Sheets("ProductReport").Visible = TrueSheets("Menu").Visible = False
    ActiveWindow.ScrollColumn = 1    ActiveWindow.ScrollRow = 1Sheet1.Protect Password:="....", AllowSorting:=True, AllowFiltering:=TrueApplication.ScreenUpdating = TrueEnd Sub

 

[Splifford1]

Sub ReporteIndividual()Application.ScreenUpdating = False
Sheet1.Unprotect Password:="BURBUJAS1"


    Dim X, Y(), i As Long, iCnt As Integer, iSecStk As Integer, dCurStk As Double, s As String
    
    s = Sheets("Menu").ComboBox1
    If s = "" Then Exit Sub
    
    X = Sheets("BDInventario").ListObjects(1).DataBodyRange
    For i = 1 To UBound(X, 1)
        If X(i, 1) = s Then
            iCnt = iCnt + 1: ReDim Preserve Y(1 To 10, 1 To iCnt)
            Y(1, iCnt) = X(i, 4): Y(2, iCnt) = X(i, 15): Y(3, iCnt) = X(i, 5): Y(4, iCnt) = X(i, 3)
            Y(5, iCnt) = X(i, 8): Y(6, iCnt) = X(i, 9): Y(7, iCnt) = X(i, 10): Y(8, iCnt) = X(i, 6)
            Y(9, iCnt) = X(i, 7): iSecStk = X(i, 12): dCurStk = dCurStk + X(i, 5)
        End If
    Next
    Application.ScreenUpdating = 0
    With Sheets("ReporteporProducto")
        .Rows(4).Resize(10000).Delete
        .[a4].Resize(iCnt, 9) = Application.Transpose(Y)
        With .UsedRange.Offset(3).Resize(.UsedRange.Rows.Count - 3)
            .Columns(2).NumberFormat = "$#,##0.000"
            .Columns(4).NumberFormat = "$#,##0.000"
            .Columns(6).NumberFormat = "[$-F800]dddd, mmmm dd, aaaa"
            .Columns(7).NumberFormat = "[$-80A]hh:mm:ss AM/PM;@"
            .Borders.Weight = 2
        End With
        .[D1] = s: .[h2] = iSecStk: .[h1] = dCurStk
        .Activate
        Application.Goto .[a1]
    End With
    Sheets("Menu").ComboBox1.ListIndex = -1




            
Sheets("ReporteporProducto").Visible = True
Sheets("Menu").Visible = False


    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 1
Sheet1.Protect Password:="BURBUJAS1", AllowSorting:=True, AllowFiltering:=True
Application.ScreenUpdating = True
End Sub

I can't edit my post back with this code, it's the same but it's extended

 

[rollis13]

Since you haven't attached a test file have a try with the blind changes I made to your macro.

Sub ReporteIndividual()
    
    Dim X, Y(), i As Long, iCnt As Integer, iSecStk As Integer, dCurStk As Double, s As String
    Dim flag As Boolean                                 '<= added
    
    Application.ScreenUpdating = False
    Sheet1.Unprotect Password:="BURBUJAS1"
    s = Sheets("Menu").ComboBox1
    If s = "" Then GoTo Finish            'could still be Exit Sub       '<= changed    
    X = Sheets("BDInventario").ListObjects(1).DataBodyRange
    For i = 1 To UBound(X, 1)
        If X(i, 1) = s Then
            iCnt = iCnt + 1: ReDim Preserve Y(1 To 10, 1 To iCnt)
            Y(1, iCnt) = X(i, 4): Y(2, iCnt) = X(i, 15): Y(3, iCnt) = X(i, 5): Y(4, iCnt) = X(i, 3)
            Y(5, iCnt) = X(i, 8): Y(6, iCnt) = X(i, 9): Y(7, iCnt) = X(i, 10): Y(8, iCnt) = X(i, 6)
            Y(9, iCnt) = X(i, 7): iSecStk = X(i, 12): dCurStk = dCurStk + X(i, 5)
            flag = True                                 '<= added
        End If
    Next
    If flag = False Then                                '<= added
        MsgBox "no match found"                         '<= added
        GoTo Finish            'could be Exit Sub       '<= added
    End If                                              '<= added
    Application.ScreenUpdating = 0
    With Sheets("ReporteporProducto")
        .Rows(4).Resize(10000).Delete
        .[A4].Resize(iCnt, 9) = Application.Transpose(Y)
        With .UsedRange.Offset(3).Resize(.UsedRange.Rows.Count - 3)
            .Columns(2).NumberFormat = "$#,##0.000"
            .Columns(4).NumberFormat = "$#,##0.000"
            .Columns(6).NumberFormat = "[$-F800]dddd, mmmm dd, aaaa"
            .Columns(7).NumberFormat = "[$-80A]hh:mm:ss AM/PM;@"
            .Borders.Weight = 2
        End With
        .[D1] = s: .[H2] = iSecStk: .[H1] = dCurStk
        .Activate
        Application.Goto .[A1]
    End With
Finish:                               'may not be necessary, see above    '<= added
    Sheets("Menu").ComboBox1.ListIndex = -1
    Sheets("ReporteporProducto").Visible = True
    Sheets("Menu").Visible = False
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 1
    Sheet1.Protect Password:="BURBUJAS1", AllowSorting:=True, AllowFiltering:=True
    Application.ScreenUpdating = True

End Sub

 

[Splifford1]

It works! thank you so much Rollis!

I want to ask another question but I guess Ill let this post go and ask it in a new post!

 

[rollis13]

Glad I was able to help .

Yes, new question but different argument, new thread .