VBA AutoFilter results in Run-Time error '1004' No cells were found." error (BUT the records ARE there)

Hi Experts,

Really hoping someone is out there on a Sunday afternoon to help me with this issue and preserve my sanity.

I have two procedures (View_Trip_Schedule and View_Hotel_Schedule) that invoke AutoFilter routines to display only those records of interest.  Both procedures Call a chgFontColor procedure that is supposed to run against only the filtered cells.  

The View_Trip_Schedule works without a problem, but the View_Hotel_Schedule ends with a Run-Time error '1004' No cells were found."  Yet after the error occurs, I can click on the AutoFilter drop-down in Cell AN8, click the already selected "Text Filters | Custom Filter", and see the criteria that was provided by the VBA.  By doing nothing else other than clicking 'OK' on the already existing criteria, the Filter then works (Instead of that, I can also just re-run the View_Hotel_Schedule procedure again and this time it works). Without knowing why this is happening (and I've tried most of the weekend to get this working), I've attempted to work around it by using the macro recorder to capture the steps I manually took (as described in the previous sentence) and then running that additional code if that Run-time error occurs.  Still no joy.

To reproduce this error, I first select the "15 FBT" ("Trip Number") value in Cell AP5 (this is the 8th item in the Data Validation list, so need to scroll the list to find that) and run the View_Trip_Schedule procedure by clicking the "Navigator" button in Cell A1, then clicking the "Select Trip" button in the Navigator Userform.  That runs without problem (Note the attached file is already in this condition as this procedure has already been run - I mention the sequence here in case it is necessary to run through this again).

I then select the "Bayerischer Hof" ("Hotel") value in Cell AP3 (this is the 3rd item in the Data Validation list) and run the View_Hotel_Schedule procedure by clicking the "Navigator" button in Cell A1, then clicking the "Select Hotel" button in the Navigator Userform.

The error occurs in the Called chgFontColor procedure in this line:

    Set filteredRng = rng.SpecialCells(xlCellTypeVisible)  '*** ERROR Occurs Here ONLY when called by View_Hotel_Schedule ***

Both of the Calling procedures serve to display only those columns with Dates (in Row 8) that correspond to either a given Trip (e.g. "15 FBT") or a given Hotel (e.g., "Bayerischer Hof") and further processing (e.g. the Called chgFontColor procedure) should occur only against the Filtered cells in those columns.  

In terms of differences between the two Calling procedures:

The View_Hotel_Schedule also has Totals for the Hotel in Rows 510:535, so there is a second AutoFilter Criteria to include Rows with "Totals" in "AN510:AN535" (Note the word "Totals" is in White font in those Cells so they are not visible).  The first AutoFilter Criteria in this procedure also uses a "Begins with" Text filter criteria because it operates against a Concatenated Field of "Hotel | Trip".  So the code for this procedure is:

    rngToFilter.AutoFilter Field:=1, Criteria1:= _
        "=" & thisHotel & "*", Operator:=xlOr, Criteria2:="=Totals", Operator:=xlFilterValues

Open in new window


... whereas the similar code for the View_Trip_Schedule procedure is simpler (because the Totals rows 510:535 are not needed and it is a straight Value comparison):

    rngToFilter.AutoFilter Field:=1, Criteria1:=Range("AP5").Value

Open in new window


Hope this is clear and someone has some insights on how I can get this working (where both procedures can successfully run).  

Jeff

EE-Question.xlsm
LVL 2
Jeffrey SmithOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

At the moment you want to find the visible cells in

ThisWorkbook.Sheets(1).Range("AR10:KA509")

they are all hidden 8 the one viewable are from row510 and further

that's why Set filteredRng = rng.SpecialCells(xlCellTypeVisible)

doesn't work

Regards
Jeffrey SmithOwnerAuthor Commented:
Thanks for posting, Rgonzo.

I don't think I made myself clear.  I've attached another version of the file: the only thing different is that I have already run the View_Trip_Schedule with the "15 FBT" Trip # in the AP5 Cell. Two records are now shown.  I have also selected the "Bayerischer Hof" ("Hotel") value in Cell AP3 (this is the 3rd item in the Data Validation list).  So that's the condition of the new "EE Question 2.xlsm" file I've attached.  If you now run the View_Hotel_Schedule procedure by clicking the "Navigator" button in Cell A1, then clicking the "Select Hotel" button in the Navigator Userform, you should produce the run-time error "No cells were found."  However, as reported in the original post:

Yet after the error occurs, I can click on the AutoFilter drop-down in Cell AN8, click the already selected "Text Filters | Custom Filter", and see the criteria that was provided by the VBA.  By doing nothing else other than clicking 'OK' on the already existing criteria, the AutoFilter then works (Instead of that, I can also just re-run the View_Hotel_Schedule procedure again and this time it works).
 When it finishes, there are four records found in the AR10:KA509 range, plus the Totals Rows in Rows 510:535 that are displayed.

So, for some reason, the AutoFilter fails to run the first time (even though the correct Filter criteria was generated by VBA).  The question is why?  And how do I fix that?

Jeff
Jeffrey SmithOwnerAuthor Commented:
Oops, forgot the 2nd file:

Jeff

EE-Question-2.xlsm
Rgonzo1971Commented:
HI,

pls try to insert

    Application.Calculate
'before 
    rngToFilter.AutoFilter Field:=1, Criteria1:= _
        "=" & thisHotel & "*", Operator:=xlOr, Criteria2:="=Totals", Operator:=xlFilterValues

Open in new window


to permit to ConcatenateHotels to work

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey SmithOwnerAuthor Commented:
Thank you,  Rgonzo, Thank you, Thank you, Thank you !!

This was driving me crazy !!

Works as it's supposed to now.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.