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
75 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
  • 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
 

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 45

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 45

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 45

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 45

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 45

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 45

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 45

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now