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

[MartinArgimon]

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

 

[mumps]

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.

 

[Fluff]

Autofiltering on a number that doesn't exist will not generate an error. You need to test for a valid number first like

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

 

[MartinArgimon]

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

 

[MartinArgimon]

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

 

[mumps]

I think that should work.

 

[RoryA]

Please take a minute to read the forum rules on cross-posting, and then comply with them. Thank you.

 

[Fluff]

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)

 

[MartinArgimon]

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 )

 

[RoryA]

Copy the web address(es) of your other post(s) into a new post here, please.