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
84 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 36

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 36

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 36

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 36

Expert Comment

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

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
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…

688 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