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

Splifford1

New Member
Joined
Jun 30, 2018
Messages
6
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:confused::confused:

HTML:
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Code:
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
 
Upvote 0
Since you haven't attached a test file have a try with the blind changes I made to your macro.
Code:
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
 
Last edited:
Upvote 0
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! :rofl:
 
Upvote 0
Glad I was able to help :).

Yes, new question but different argument, new thread ;).
 
Upvote 0

Forum statistics

Threads
1,224,768
Messages
6,180,850
Members
453,002
Latest member
anifnisar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top