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
79 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 46

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 46

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

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 46

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 46

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 46

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

914 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

14 Experts available now in Live!

Get 1:1 Help Now