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

 

[MartinArgimon]

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

Hi Rory,
Please be patient with me, since I'm new to this forum's rules ( sorry).
I really do not understand what u mean by other web addresses of my other posts.
I am only dealing with you ( Mr. Excel ) . ( within Mr.excel I had you, Fluff, and mumps addressing my issue.)
Are u referring to them?
Kindly elaborate so i can comply
Sorry for the trouble
Thanks
Martin

 

[mumps]

Try:

Private Sub CmdbuttonPurchaseSummary_Click()
    Application.ScreenUpdating = False
    Dim sh As Worksheet, dsh As Worksheet, ordernumber As Integer, fnd As Range
    Set sh = ThisWorkbook.Sheets("Beneficiaries_Burials")
    Set dsh = ThisWorkbook.Sheets("Purchase_Summary")
    ordernumber = Application.InputBox(Prompt:="Enter Order Number", Type:=1)
    Set fnd = sh.Range("B:B").Find(ordernumber, LookIn:=xlValues, lookat:=xlWhole)
    If fnd Is Nothing Then
        MsgBox ("Order Number not found.  Please try again.")
        Exit Sub
    End If
    dsh.Range("C21:R400").ClearContents
    With sh
        .AutoFilterMode = False
        .Range("Beneficiaries_Burials").AutoFilter Field:=1, Criteria1:=ordernumber
        .Range("Beneficiaries_Burials").Copy
    End With
    With dsh
        .Range("C11").Value = ordernumber
        .Range("C21").PasteSpecial xlPasteValues
    End With
    With sh
        .AutoFilterMode = False
        .ShowAllData
    End With
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

What Rory is saying is that you have asked this question on another site. That is called crossposting and you need to supply a link to the other site(s) where you have asked this question.
Please read this to understand about crossposting https://www.excelguru.ca/content.php?184

 

[MartinArgimon]

What Rory is saying is that you have asked this question on another site. That is called crossposting and you need to supply a link to the other site(s) where you have asked this question.
Please read this to understand about crossposting https://www.excelguru.ca/content.php?184

I see, OK
sorry I am new to any FORUMS.
I will then stick to Mr Excel . NO Problem
You will advise me if and when i need to search for answers anywhere else.
Many thanks for warning me.

In the meantime I have asked you if you weer able to retrieve my file from One Drive, and I also mentioned what did not work
I be on stanby
many thanks
Rgds
martini argimon

 

[RoryA]

That's not really the point, I'm afraid. You need to post the links - eg:

https://www.myonlinetraininghub.com...ter-that-doesnt-match-the-values-in-the-range

Any others?

 

[MartinArgimon]

Try:

Private Sub CmdbuttonPurchaseSummary_Click()
    Application.ScreenUpdating = False
    Dim sh As Worksheet, dsh As Worksheet, ordernumber As Integer, fnd As Range
    Set sh = ThisWorkbook.Sheets("Beneficiaries_Burials")
    Set dsh = ThisWorkbook.Sheets("Purchase_Summary")
    ordernumber = Application.InputBox(Prompt:="Enter Order Number", Type:=1)
    Set fnd = sh.Range("B:B").Find(ordernumber, LookIn:=xlValues, lookat:=xlWhole)
    If fnd Is Nothing Then
        MsgBox ("Order Number not found.  Please try again.")
        Exit Sub
    End If
    dsh.Range("C21:R400").ClearContents
    With sh
        .AutoFilterMode = False
        .Range("Beneficiaries_Burials").AutoFilter Field:=1, Criteria1:=ordernumber
        .Range("Beneficiaries_Burials").Copy
    End With
    With dsh
        .Range("C11").Value = ordernumber
        .Range("C21").PasteSpecial xlPasteValues
    End With
    With sh
        .AutoFilterMode = False
        .ShowAllData
    End With
    Application.ScreenUpdating = True
End Sub

Mumps,
Thanks very much for your code .
Your code works 100%
One again, sorry if i did not know the rules about cross posting.
( Believe me i'm not a chancer... I am 60 years old)

I only had to search somewhere else other then the other two sites ( since they were not able to help me).!
Many thanks again for your code solution.
I desperately needed some help since as you can see my knowledge of VBA is limited.

Sorry once again to Fluff, Rory and you
Kind Regards
I'll stay in comms.
Martin Argimon

 

[mumps]

Glad it all worked out.

 

[MartinArgimon]

Glad it all worked out.

Many Thanks again
I can send you the 'solution' that the other site "myonlinetraining' proposed to me, that did not work, as rory suggested, if you want.
Is up to you
Let me know
Kind regards again