Solved

When I run my excel macro to try and update/caculate a sheet I get an error message. Why?

Posted on 2015-02-18
18
82 Views
Last Modified: 2015-02-18
Hello,

When I try to run a macro to update a sheet in the file I get an error message stating:

Run-time error '32809':
Application-defined or object-defined error

This spreadsheet use to run fine last week but not now. I am not sure why. Could you please assist?

Thanks.
0
Comment
Question by:f19l
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
18 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 40616413
Don't run the macro, use the Step in command, then keep pressing F8 (might take a while) to go through the code step by step. When it throws an error, post the code online, and tell us which line threw the error.
0
 

Author Comment

by:f19l
ID: 40616424
When I go through step by step and reach the line relating to calculating/updating the sheet "Cap Floor" it stops and I get the error message.

Sub Prepare()

ActiveSheet.Calculate

Sheets("Cap Floor").Calculate
   
End If

End Sub
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40616522
It probably means the original sheet named "Cap Floor" has either been deleted or renamed. Can you double check the existence of this sheet?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:f19l
ID: 40616530
The sheet name is correct. I even renamed the sheet and changed the macro accordingly and I still get the same problem.
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40616548
Anything on that Cap Floor sheet (added button/etc from the MS Forms)?
0
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40616562
The sheet may be corrupted. Add a new sheet (don't use "Move or Copy") and copy everything from the old sheet to the new sheet. Run your code on the new sheet and see if that works. If it does you can delete the old sheet.
0
 

Author Comment

by:f19l
ID: 40616568
The only thing is a scroll bar for changing the time and a drop down box for changing the status location. Otherwise there is nothing else.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40616574
Did you try my suggestion?
0
 

Author Comment

by:f19l
ID: 40616667
Just tried. That works now. I will go ahead and make the necessary changes. Thanks.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40616727
This is probably the fault of the environment I'm working in but when I download your zip file I see this as its contents.

???
Could you please upload it to some file-sharing site like MediaFire or SpeedyShare and post the resulting URL?
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40616729
You tested the new sheet with those scroll bar and dropdown elements already?
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40616737
The screenshot actually shows the excel file (which means you downloaded it correctly), just rename to xlsx to get it working.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40616738
Sorry but please ignore post ID: 40616727. I posted in the wrong thread.
0
 

Author Comment

by:f19l
ID: 40616743
The solution was to create a new tab, copy and paste the old formula and then rename.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40616744
@Kimputer: Thanks, I just figured that out. I actually just removed the .zip extension and let Excel figure out what it was.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40616751
@f19l: sorry for the confusion I caused but will you be closing this question?
0
 

Author Comment

by:f19l
ID: 40616758
I have accepted your post as the solution so the question should be closed now.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40616778
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question