I have a working Excel 2007 macro that accesses two worksheets. I then added a third worksheet and want to access it from the macro. I get a debug error 13 Type Mismatch when the macro tries to access the third worksheet. I know I need to add the third worksheet on a pop-up but I don't remember how to open it. Help!!!
Welcome to the forum.
Pardon?I know I need to add the third worksheet on a pop-up ...
Entia non sunt multiplicanda sine necessitate
Were you asking about my reference to a pop-up?
That confused me too. What code actually triggers the error though?
Everyone who confuses correlation and causation ends up dead.
The error is triggered when the third worksheet is referenced. If I coment out the third Gosub the macro works. When I added the second sheet I found a way to add a worksheet but I can't remember it or find it now . The way included selecting something in the project window and typing a Cntl and another key. This brought up a window that allowed me to add the worksheet.
I had to shorten my code to include it here:
Please Login or Register to view this content.
Last edited by BillGoodman; 06-23-2009 at 10:51 AM.
You shouldn't get a type mismatch error if the sheet doesn't exist, you should get a subscript out of range. However, you are looking for sheets 1, 3 and 5 so you'll need more than 3 sheets anyway. Can't you just add them through the user interface by clicking the new sheet button?
I have no idea why you are using GoSub either which is somewhat outdated and makes for unnecessarily convoluted code.
The use of the Gosub is out of habit.
Worksheets 1, 2, 3, 4, 5, and 6 are defined. I had the macro working worksheets 1 and 2. Then I added 3 and 4. I had the same problem, but I found a way in one of the help pages to configure the macro to accept 3 and 4 and it worked. Now I've added 5 and 6 and can't remember what I did to fix it before. I guess I'm having a Senior Episode.
You still didn't say which line actually triggers the error. If it's this one:
then you probably have an error value in that cell.Please Login or Register to view this content.
Bill,
Please take a few minutes to read the forum rules, and then edit post#5 to add code tags.
Please Login or Register to view this content.
Last edited by BillGoodman; 06-23-2009 at 10:52 AM.
See my last post then - sounds like there is an error in that cell.
There is an error in the rangetopoke. How can I clear it? Worksheet 5 is in the Workbook, the macro doesn't acknowledge it.
Last edited by BillGoodman; 06-23-2009 at 10:54 AM.
You could add an error handler to the code - for example:
Please Login or Register to view this content.
The error is due to sheetNumber being set to 5.
No, it's not. It's due to there being an error value in that cell, otherwise the error would not occur on that line.
The cell contains a value of 65535. The same cell on worksheets 1 and 3 contain 65535.
If, as you said, this line is causing a type mismatch error:
then the value can't be 65535. Are you sure you are checking the right sheet? What do you get if you add this line before that one:Please Login or Register to view this content.
Please Login or Register to view this content.
There is an Error 2042 in rangeToPoke when that line is executed with sheetNumber = 5.
So did you add the error check I suggested?
With your suggested code added I get the "Debug Error 13 Type Mismatch" error when it is executed. I added a watch for rangeToPoke, rangeToPoke.Value, and sheetNumber. After the error occurs they contain the following:
rangeToPoke: Error 2042
rangeToPoke.Value: Error 2042
sheetNumber: 5
Can you post the current code you have, just so I can check it?
Bill,
Are you attempting to reference a worksheet named "5", or Worksheets(5)?
Please Login or Register to view this content.
So you didn't add the error handler I suggested earlier?
I am attempting to access the 5th worksheet (Sheet5). Worksheets(5).
Last edited by BillGoodman; 06-23-2009 at 12:26 PM.
I did add the error handler and I got a Run-Time Error 424 Object Required. I have been trying some things and I took it out along the way.
I don't see how you could get a 424 error with what I posted - where exactly did you put it?
Just above the MsgBox line.
This code works:
This uses the names of the sheets instead of the numbers.Please Login or Register to view this content.
RomperStomper,
Thanks for your help!
No problem - glad you got it fixed!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks