Loop increment help required

sparky2205

sparky2205

Well-known Member
Joined
Feb 6, 2013
Messages
507
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi folks,
I have a spreadsheet that requests a user enter a value for a vendor number.
The user has the option to first select the number of vendors.
I have some error proofing, e.g. if the user cancels or doesn't enter a vendor number.
What I'm trying to achieve is that when the user makes an error they are returned to the input box.
My issue is that my counter doesn't increment correctly when this happens.
A user can enter 3 as the number of vendors but once they make a mistake this increases the number of vendors they can enter.
There are a couple of commented out lines where I was trying to work it out.

In summary; if a user enters 3 as the number of vendors, they should only be able to enter 3 vendors regardless of whether or not they make a mistaken entry, i.e. blank, cancel or incorrect vendor name.

This is the code:
VBA Code: 
Sub Filter_Vendor()

Dim vendor As Variant
Dim vendorrow As Long
Dim lastrow As Long
Dim number As Variant
Dim i As Long

        ' Find the last populated row
        lastrow = Cells(Rows.Count, 3).End(xlUp).Row
        ' Hide all vendors
        Rows("9:" & lastrow).EntireRow.Hidden = True
        ' Get the number of Vendors to be filtered from the user
        number = Application.InputBox("How many Vendors do you wish to filter?", , , , , , , 1)
        If number = False Then
                Exit Sub
                ' If the user clicks on OK but doesn't enter a Vendor Number
                ElseIf number = "" Then
                MsgBox ("You have not entered a Vendor Number")
                Exit Sub
                ElseIf Not IsNumeric(number) Then
                    MsgBox "A number >0 is required here"
                Exit Sub
        End If
vvendor:    For i = 1 To number
            ' Get the vendor number from the user
            vendor = Application.InputBox("Enter the Vendor Number", , , , , , , 2)
            ' If user clicks Cancel
                If vendor = False Then
                    ' Hide the "Filter by Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 3").Visible = False
                    ' Show the "Unfilter Vendors" screen button
                    ActiveSheet.Shapes("Rectangle 5").Visible = True
                    ' Show the "Update Filter" screen button
                    ActiveSheet.Shapes("Rectangle 4").Visible = False
                    ' Hide the "Unfilter Specific Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 6").Visible = False
                    'i = i - 1
                    'GoTo vvendor
                Exit Sub
                    ' If the user clicks on OK but doesn't enter a Vendor Number
                ElseIf vendor = "" Then
                    MsgBox ("You have not entered a Vendor Number")
                    ' Hide the "Filter by Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 3").Visible = False
                    ' Show the "Unfilter Vendors" screen button
                    ActiveSheet.Shapes("Rectangle 5").Visible = True
                    ' Show the "Update Filter" screen button
                    ActiveSheet.Shapes("Rectangle 4").Visible = False
                    ' Hide the "Unfilter Specific Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 6").Visible = False
                    'i = i - 1
                    'GoTo vvendor
                'Exit Sub
                ElseIf IsError(Application.Match(vendor, Sheets("Revised").Range("C:C"), 0)) Then
                    MsgBox "Selected vendor" & " '" & vendor & "' " & "does not exist"
                    ' Hide the "Filter by Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 3").Visible = False
                    ' Show the "Unfilter Vendors" screen button
                    ActiveSheet.Shapes("Rectangle 5").Visible = True
                    ' Show the "Update Filter" screen button
                    ActiveSheet.Shapes("Rectangle 4").Visible = False
                    ' Hide the "Unfilter Specific Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 6").Visible = False
                'Exit Sub
                GoTo vvendor
                Else
                ' Get the row number of the selected vendor
                vendorrow = Application.Match(vendor, Sheets("Revised").Range("C:C"), 0)
            'End If
                End If

            ' Unhide the selected vendor row plus the next 3 rows
            Rows(vendorrow & ":" & (vendorrow + 3)).Hidden = False
'            i = i + 1
        Next i
            ' Hide the "Filter by Vendor" screen button
        ActiveSheet.Shapes("Rectangle 3").Visible = False
        ' Show the "Unfilter Vendors" screen button
        ActiveSheet.Shapes("Rectangle 5").Visible = True
        ' Show the "Update Filter" screen button
        ActiveSheet.Shapes("Rectangle 4").Visible = True
        ' Hide the "Unfilter Specific Vendor" screen button
        ActiveSheet.Shapes("Rectangle 6").Visible = True
End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Try shifting to a different kind of loop (not tested).
VBA Code: 
Sub Filter_Vendor()

Dim vendor As Variant
Dim vendorrow As Long
Dim lastrow As Long
Dim number As Variant
Dim i As Long

        ' Find the last populated row
        lastrow = Cells(Rows.Count, 3).End(xlUp).Row
        ' Hide all vendors
        Rows("9:" & lastrow).EntireRow.Hidden = True
        ' Get the number of Vendors to be filtered from the user
        number = Application.InputBox("How many Vendors do you wish to filter?", , , , , , , 1)
        If number = False Then
                Exit Sub
                ' If the user clicks on OK but doesn't enter a Vendor Number
                ElseIf number = "" Then
                MsgBox ("You have not entered a Vendor Number")
                Exit Sub
                ElseIf Not IsNumeric(number) Then
                    MsgBox "A number >0 is required here"
                Exit Sub
        End If
        
If number <= 0 Then number = 1
i = 1
vvendor:
    Do While i <= number
            ' Get the vendor number from the user
            vendor = Application.InputBox("Enter the Vendor Number", , , , , , , 2)
            ' If user clicks Cancel
                If vendor = False Then
                    ' Hide the "Filter by Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 3").Visible = False
                    ' Show the "Unfilter Vendors" screen button
                    ActiveSheet.Shapes("Rectangle 5").Visible = True
                    ' Show the "Update Filter" screen button
                    ActiveSheet.Shapes("Rectangle 4").Visible = False
                    ' Hide the "Unfilter Specific Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 6").Visible = False
            
                    Select Case MsgBox("Quit macro?", vbYesNo Or vbQuestion, Application.Name)
                    Case vbYes
                      Exit Sub
                    Case vbNo
                   End Select
                   GoTo vvendor
            
                    ' If the user clicks on OK but doesn't enter a Vendor Number
                ElseIf vendor = "" Then
                    MsgBox ("You have not entered a Vendor Number")
                    ' Hide the "Filter by Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 3").Visible = False
                    ' Show the "Unfilter Vendors" screen button
                    ActiveSheet.Shapes("Rectangle 5").Visible = True
                    ' Show the "Update Filter" screen button
                    ActiveSheet.Shapes("Rectangle 4").Visible = False
                    ' Hide the "Unfilter Specific Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 6").Visible = False
                    GoTo vvendor


                ElseIf IsError(Application.Match(vendor, Sheets("Revised").Range("C:C"), 0)) Then
                    MsgBox "Selected vendor" & " '" & vendor & "' " & "does not exist"
                    ' Hide the "Filter by Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 3").Visible = False
                    ' Show the "Unfilter Vendors" screen button
                    ActiveSheet.Shapes("Rectangle 5").Visible = True
                    ' Show the "Update Filter" screen button
                    ActiveSheet.Shapes("Rectangle 4").Visible = False
                    ' Hide the "Unfilter Specific Vendor" screen button
                    ActiveSheet.Shapes("Rectangle 6").Visible = False
                    GoTo vvendor
                Else
                  i = i + 1
                  
                  ' Get the row number of the selected vendor
                  vendorrow = Application.Match(vendor, Sheets("Revised").Range("C:C"), 0)
                End If

            ' Unhide the selected vendor row plus the next 3 rows
            Rows(vendorrow & ":" & (vendorrow + 3)).Hidden = False
        Loop
        
            ' Hide the "Filter by Vendor" screen button
        ActiveSheet.Shapes("Rectangle 3").Visible = False
        ' Show the "Unfilter Vendors" screen button
        ActiveSheet.Shapes("Rectangle 5").Visible = True
        ' Show the "Update Filter" screen button
        ActiveSheet.Shapes("Rectangle 4").Visible = True
        ' Hide the "Unfilter Specific Vendor" screen button
        ActiveSheet.Shapes("Rectangle 6").Visible = True
End Sub
 
Upvote 0
Solution
What can I say? Beautiful, just beautiful. Works perfectly.
It never occurred to me to change the loop type.
Thank you for your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

sparky2205
  • Solved
Object variable or With Block variable not set
2
Replies
19
Views
507
Alex Blakenburg
A
P
  • Solved
Disable 'X' on Worksheet
Replies
4
Views
168
Pacman52
P
E
  • Question Question
Can you combine these 2 macros ?
Replies
6
Views
217
excel4us
E
A
  • Question Question
I'm trying to float a button at the bottom of an Excel table
Replies
1
Views
883
skillilea
S
W
  • Question Question
Creating a 'Reset' button for a text form, plus additional buttons
Replies
2
Views
304
WEB78
W

Forum statistics

Threads
1,224,755
Messages
6,180,758
Members
452,996
Latest member
nelsonsix66

Share this page

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