Excel VBA "Object or With Block variable not set"


Hi there,  I can't figure out why my code stops on the second line of code (tbldata2VisCount) and not on the first? I hover over tbldataVisCount and it shows the correct count of filtered items but when I hover over tbldata2VisCount, the result = Emply

 Dim tbldataVisCount As Variant
 Dim tbldata2VisCount As Variant


 tbldataVisCount = Sheet4.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
 tbldata2VisCount = Sheet2.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1    ' this line keeps breaking
            dblCount = tbldataVisCount + tbldata2VisCount

Any thoughts?
Calvin LeBlancReporting EngineerAsked:
Who is Participating?
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.

ioanePlanning & Analytics ManagerCommented:
Make sure you have a Sheet2 in your workbook.

Go to vba editor and check the names of the worksheets in the project tree.

Note, you want the Object instance name, not the Worksheet name.

Another way to write it that may work better is to use the Worksheet name:

 tbldataVisCount = Sheets("Sheet_Name").AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
0
Calvin LeBlancReporting EngineerAuthor Commented:
The workbook does have a Sheet2. I just tried using the sheet name and no go.
I tried to compare how the previous line is written and all seams to be fine.
0
Calvin LeBlancReporting EngineerAuthor Commented:
I"m basically wanting to count the number of result rows  in a "Filtered" table in two separate tables (tbl1data and tbl2data) and then add those two results to be used as a reference.  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DaveCommented:
Can you pls post your file

Cheers
Dave
0
Calvin LeBlancReporting EngineerAuthor Commented:
Dave, in attempting to sanitize the raw data in the workbook I restarted Excel and it seams to be working fine now. I'll test it a little more and post a status tomorrow afternoon. thanks for the assist !!
0
ioanePlanning & Analytics ManagerCommented:
Are you using AutoFilter in both tables?

See what you get when you add this:

Debug.print "Sheet4 AutoFilter is: " & iif(Sheet4.AutoFilterMode,"on","off")
 tbldataVisCount = Sheet4.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
Debug.print "Sheet2 AutoFilter is: " & iif(Sheet2.AutoFilterMode,"on","off")
 tbldata2VisCount = Sheet2.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1    ' this line keeps breaking
0

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
Calvin LeBlancReporting EngineerAuthor Commented:
Thanks for the post Tramtrak. Your solution seams to work too. I'll do more testing and post / divy up points tomorrow evening.

Thanks to you both for the assistance.
0
DaveCommented:
If the file is working now then we haven't solved anything - so you delete your question and get your points refunded. :)

Cheers

Dave
0
Calvin LeBlancReporting EngineerAuthor Commented:
As an FYI, these lines of code seam to work fine on a data set of 20 records (in both tables) but breaks with a data set of 250,000 records in each of the tables.
still testing.
0
Rory ArchibaldCommented:
Have you tried defining all your counter variables as Long?
0
Rory ArchibaldCommented:
Also if you are using tables in excel 2007 you really want the autofilter property of the listobject, not the worksheet.
Regards
Rory
0
Calvin LeBlancReporting EngineerAuthor Commented:
Both solutions helped me better understand how to troubleshoot and resolve the issue.
Thanks to the both of you for the assistance.
0
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
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.