• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

ON error for "autofilter" that doesn't match the values in the range|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / ON error for "autofilter" that doesn't match the values in the range|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosON error for "autofilter" that does…
sp_PrintTopic sp_TopicIcon
ON error for "autofilter" that doesn't match the values in the range
Avatar
Martin Argimon
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 16, 2019
sp_UserOfflineSmall Offline
1
June 16, 2019 - 2:09 am
sp_Permalink sp_Print

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

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1529
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
June 16, 2019 - 3:26 pm
sp_Permalink sp_Print

Hi Martin,

Please supply the workbook so we can help.

Phil

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
3
June 17, 2019 - 5:32 pm
sp_Permalink sp_Print

Filtering for a value that isn't there will not cause an error, you just get no data shown. You can test for that by checking there is more than one visible cell in a column of the filtered data (the header cell should always be visible):

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

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

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

sh.AutoFilterMode = False
With sh.Range("Beneficiaries_Burials")
.AutoFilter Field:=1, Criteria1:=ordernumber
If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
dsh.Range("C11").Value = ordernumber
.Copy
dsh.Range("C21").PasteSpecial xlPasteValues
Else
MsgBox "you have entered the wrong Order Number"
End If
End With
sh.AutoFilterMode = False
sh.ShowAllData

End Sub

Avatar
Martin Argimon
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 16, 2019
sp_UserOfflineSmall Offline
4
June 18, 2019 - 8:24 am
sp_Permalink sp_Print sp_EditHistory

HI Philip

Herewith my excel sheet attached.

Briefly this Program is for the Purchasing of Parcels / Graves in a Memorial Park. The products ( see Products Sheet) , are usually a single parcel or a "family garden or estate, etc. It means that when the buyer purchases a Product for his family ( in the case of a Daily of 6 individuals ) , these persons are listed under the Beneficiaries_Burials Table.

I do not want to overload you with info, but if you need further clarification , please let me know

PROBLEM STATEMENT:

If you go to the 'Purchase_Summary' sheet, you will find the Code saved under this sheet. You can click the "Enter Order Number" button ( top right corner) to select the order number to get all the info related to aN specific order . It ONLY works if i enter exactly any of the order numbers listed in the "Orders" sheet. If i enter any other number which is NOT in the orders sheet it copies "ALL" the records that are extracted from the "Beneficiaries_Burials" sheet, although i tried to include the 'On ERROR' statement as well as the "if Nothing" statement to avoid this.

NOT SURE how to get the code to pause or retry, if i enter a wrong order number or even the cancel button.

Thanking you in advance

Avatar
Martin Argimon
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 16, 2019
sp_UserOfflineSmall Offline
5
June 19, 2019 - 1:22 am
sp_Permalink sp_Print

HI Velouria & Philip

I have sent the attachment of my Excel file yesterday. Did you receive it ?

Kindly confirm

Thx

Martin Argimon 

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
6
June 19, 2019 - 4:57 pm
sp_Permalink sp_Print

No, I see no attachment. Did you try the code I suggested? If so, what happened?

Avatar
Martin Argimon
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 16, 2019
sp_UserOfflineSmall Offline
7
June 21, 2019 - 7:36 am
sp_Permalink sp_Print

HI Velouria

I've tried uploading my excel file , but is rejecting it since it exceeds the size limit. My file is 1,4 MB

I wish you could see the entire program is actually a relational database with various tables and interfaces.

I do understand your suggestion, but the problem is that if do not enter an 'existing ' order number the auto filter pushes a lot of data in to my query which is not what i want.

Is difficult for me to explain the entire problem, I wish i could send you the file , which is very easy to understand.

Any other ways that I can attach a larger file ?

Kindly let me know

Many Thanks

Kind regards

Martin Argimon 

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
8
June 21, 2019 - 5:40 pm
sp_Permalink sp_Print

You didn't answer my question: did you actually try the code I suggested? If so, what happened? If not, why not? 😉

Avatar
Martin Argimon
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 16, 2019
sp_UserOfflineSmall Offline
9
June 21, 2019 - 6:56 pm
sp_Permalink sp_Print

Velouria.

I just to summarise my question. I just want the "On error" statement to work within my macro at the moment if i enter the wrong order number does not stop and does not ask me to retry.

Kindly check my code below and let me know where i went wrong?

Many Thanks

Martin Argimon ( code below)

 

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

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
10
June 22, 2019 - 5:09 pm
sp_Permalink sp_Print

That is not the code I suggested. Please at least try what I posted.

Avatar
Martin Argimon
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 16, 2019
sp_UserOfflineSmall Offline
11
June 23, 2019 - 8:58 pm
sp_Permalink sp_Print

Sorry

Your suggestion is NOT applicable

You insist on telling me something that is NOT my problem

I told you that is the 'On Error" what does not work

You do not need to be so Proud !!!

regards

Martin Argimon 

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Cassie Bernier
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 215
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Ngoc Qui Nguyen
Clement Mansfield
Rose .
Bindu Menon
Baruch Zemer
Purple RainbowBenefactor
MOTH Junkie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6350
Posts: 27773

 

Member Stats:
Guest Posters: 49
Members: 32317
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.