Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1228
  • Last Modified:

Application Defined (40036) Error when using Sheets("xxx").Select

When I use:

Sheets("name").Select
or
Worksheets("name").Activate

I get a runtime error:
Application Defined or Object Defined Error (#40036)

What is wierd about this is that it is part of a loop that does the same steps to several different worksheets and uses a variable for the sheet name, for instance:

Function ProcessSheet(strSite as string)
   windows("windowname").activate
   sheets(strSite & "restofname").select
   'do some action on the sheet
end function

When it runs the FIRST TIME THROUGH it concatenates the sheet name with the 'restofname' part and runs just FINE.  The second call of the function causes the error listed above.

Is this a reference problem?  I am lost, please advise...

Cid



0
arthurcid
Asked:
arthurcid
  • 5
  • 3
1 Solution
 
bruintjeCommented:
Hi Arthurcid,

-this could a corrupted sheet in the book
-what happens when you insert a new sheet
-copy the contents of the problem sheet to the new one
-delete the second sheet that gives the problem
-and name the new sheet like the problem one

-is this sitll giving errors?
-or is there something different like the third sheet giving a problem?

HAGD:O)Bruintje
0
 
arthurcidAuthor Commented:
No, it is not a corrupt sheet.

I have tried several different approaches (using newly created sheets, etc.).

Bottom line is this:

This :  sheets(var & "text.xls").activate

will NOT work if you do it TWICE in the same macro.  

In other words,

1.  Run that line (using "John" for var)
2.  Do a loop
3.  Run that line (using "Bill" for var)

I get application defined error every time it runs the SECOND time.  Hell, even during DEBUG MODE i can change it to :

sheets("Johntext.xls").activate
RIGHT IN THE MIDDLE OF DEBUGGING (WHILE THE CODE IS STILL RUNNING) and still get the error when I try to 'step' through it after the change.

I TOTALLY DONT UNDERSTAND WHY IT WORKS JUST FINE THE FIRST PASS THROUGH AND THEN ON SUBSEQUENT PASSES I GET THE ERROR.

Really pissing me off and nobody is replying to this, so I am about to give up.

I will GLADLY give several hundred MORE points for an valid answer to this question.

Cid
0
 
arthurcidAuthor Commented:
No, it is not a corrupt sheet.

I have tried several different approaches (using newly created sheets, etc.).

Bottom line is this:

This :  sheets(var & "text.xls").activate

will NOT work if you do it TWICE in the same macro.  

In other words,

1.  Run that line (using "John" for var)
2.  Do a loop
3.  Run that line (using "Bill" for var)

I get application defined error every time it runs the SECOND time.  Hell, even during DEBUG MODE i can change it to :

sheets("Johntext.xls").activate
RIGHT IN THE MIDDLE OF DEBUGGING (WHILE THE CODE IS STILL RUNNING) and still get the error when I try to 'step' through it after the change.

I TOTALLY DONT UNDERSTAND WHY IT WORKS JUST FINE THE FIRST PASS THROUGH AND THEN ON SUBSEQUENT PASSES I GET THE ERROR.

Really pissing me off and nobody is replying to this, so I am about to give up.

I will GLADLY give several hundred MORE points for an valid answer to this question.

Cid
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
arthurcidAuthor Commented:
No, it is not a corrupt sheet.  Same problem OVER AND OVER again even using sheets made from scratch.

THIS IS REALLY PISSING ME OFF, ID BE GLAD TO GIVE SEVERAL HUNDRED MORE POINTS FOR THIS IF SOMEONE COULD PROVIDE A CORRECT RESOLUTION.

0
 
arthurcidAuthor Commented:
No, it is not a corrupt sheet.  Same problem OVER AND OVER again even using sheets made from scratch.

THIS IS REALLY PISSING ME OFF, ID BE GLAD TO GIVE SEVERAL HUNDRED MORE POINTS FOR THIS IF SOMEONE COULD PROVIDE A CORRECT RESOLUTION.

0
 
arthurcidAuthor Commented:
oops, sorry...

this: sheets(var & "text.xls").activate
should be
This :  sheets(var & "text").activate

and sorry for the double posts, my internet at work was being buggy so i couldnt tell if they were posting or not.
0
 
bruintjeCommented:
been some time but i was gone for a while from active participation in july

what i did was in a excel workbook tested this code

Option Explicit

Function ProcessSheet(strSite As String)
  Windows(1).Activate
  Sheets("Sheet" & strSite).Select
  'do some action on the sheet
End Function

Sub t()
Dim i As Integer
For i = 1 To 2
  ProcessSheet (i)
Next
End Sub

where sub t calls the processsheet function and it works for me, so what's done in the rest of your processing is there a sheet renamed or copied or something

see if the snippet from above works for you if it does then the error comes from later processing then the selection

:O)Bruintje
0
 
bruintjeCommented:
Hello arthurcid

this question is open for more then 2 months
time to clean up
if not stated otherwise

my recom will be
-PAQ and no refund
-this will be finalized by an EE Moderator
-with no further update (28.09.2002)

PLEASE DO NOT ACCEPT THIS COMMENT AS ANSWER

HAGD:O)Bruintje
posted by ToolzEE v1.0
0
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now