excel vba "On Error go to" while in an Autofilter procedure

MartinArgimon

New Member
Joined
Jun 23, 2019
Messages
8
HI There,
My code does NOT stop with the "On Error...." code, when i type a 'non-existant' order number. It does not take my back to "Re-try".
Do i have the "On error..." code in the wrong slot within the overall code of the procedure?
See below.
Many thanks

Private Sub CmdbuttonPurchaseSummary_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Beneficiaries_Burials")
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Purchase_Summary")
Dim ordernumber As Integer

'TryAgain:
ordernumber = Application.InputBox(Prompt:="Enter Order Number", Type:=1)

dsh.Range("C21:R400").ClearContents

sh.Activate
sh.AutoFilterMode = False
'On Error GoTo TryAgain
sh.Range("Beneficiaries_Burials").AutoFilter Field:=1, Criteria1:=ordernumber
'On Error GoTo 0
sh.Range("Beneficiaries_Burials").Copy

dsh.Activate
dsh.Range("C11").Value = ordernumber
dsh.Range("C21").PasteSpecial xlPasteValues

sh.AutoFilterMode = False
sh.ShowAllData

'Exit Sub

'TryAgain:
' MsgBox "you have entered the wrong Order Number"

End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Can you describe in words what you are trying to do with a detailed explanation referring to specific cells, rows, columns and worksheets. Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Better still, perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
Autofiltering on a number that doesn't exist will not generate an error. You need to test for a valid number first like
Code:
Dim OrderNumber As [COLOR=#ff0000]Variant[/COLOR]
Dim Flg As Boolean

Do While Flg = False
   OrderNumber = Application.InputBox(prompt:="Enter Order Number", Type:=1)
   If OrderNumber = False Then Exit Sub
   If Application.CountIf(Range("A:A"), OrderNumber) > 0 Then Flg = True
Loop
dsh.Range("C21:R400").ClearContents

Sh.Activate
Sh.AutoFilterMode = False
Sh.Range("Beneficiaries_Burials").AutoFilter Field:=1, Criteria1:=OrderNumber
Make sure you make the change in red, otherwise you could end up in a permanent loop
 
Last edited:
Upvote 0
Can you describe in words what you are trying to do with a detailed explanation referring to specific cells, rows, columns and worksheets. Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Better still, perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

HI Mumps. !
If i send you a link / or share my file via MS OneDrive , would it work for u ?
Will you be abel to download it?
Please confirm thanks
Martin Argimon
 
Upvote 0
HI Fluff ( Moderator)
Unfortunately it still does not work with your suggestions.
May I send you a link and share my Excel file via MS One Drive with you , and perhaps you will be able to see the mechanics of the entire program?
Kindly confim
Many Thanks
Regards
Martin Argimon
 
Upvote 0
I think that should work.
 
Upvote 0
Please take a minute to read the forum rules on cross-posting, and then comply with them. Thank you. :)
 
Upvote 0
Please explain what "doesn't work" means
If you want to supply a file, simply post a link to it in the thread.
Do not share files privately.

Also as mentioned by RoryA, please supply links to your other thread(s)
 
Upvote 0
Please explain what "doesn't work" means
If you want to supply a file, simply post a link to it in the thread.
Do not share files privately.

Also as mentioned by RoryA, please supply links to your other thread(s)

HI THERE,
Herewith below link to my file saved in the MS One drive cloud.
I'm not sure if you 'll be able to open it, unless I enter your email address in my One drive screen ( in order to share the file with u).
Please try the link , or otherwise send me your email address?
Below my explanation of what des not work

https://grupocma001-my.sharepoint.c...ZLnaPwzURs1n4BTegyFp70wwN8SS-5wEChsQ?e=lRuo5L

I enter exactly what u suggested ( including the 'variant' change, but the Application.Input box asking me to enter the order number keeps on looping after i enter any Order number. ( it does not proceed to the next set of instructions
Many Thanks for you help
Regards
Martin

P.S: Kindly let me know how do i supply links to my other threads ? ( as u required by you )
 
Upvote 0
Copy the web address(es) of your other post(s) into a new post here, please.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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