jwise
04-22-2008, 07:10 AM
I have the following code in the loop that is examining each row in a worksheet. In most cases there is another worksheet in this same workbook that matches the column "A" contents. Since I expect that some of these worksheets are not present, I used the "On Error" statement to go to a "MsgBox" piece of code. I basically ignore this situation and continue with the next row.
The first time this occurs, I see the display, and all works as planned. This is about row 8. The next time it happens (at about row 25), I get an object error on the "Set" statement. The error is that there is no worksheet with this name. I added the "On Error" statement inside the loop because I thought that maybe once the "On Error" was executed, it was no longer valid. I can not get around this error. Any clues as to what I've done wrong? Here is the relevant code which is inside a loop going through all the rows in a particular worksheet:
On Error GoTo Recover
Set destSheet = Sheets(StrConv(dataSheet.Cells(i, 1), vbUpperCase))
As I mentioned, within the first 25 rows this occurs twice. The first time I get the message, but the second I get the message on the Set that indicates there is no worksheet by that name.
FYI: The "StrConv" was added because the very first data worksheet is named "427". The "Sheets" clause somehow converted this into an integer and attempted to access "Sheets(427)". So I added the "StrConv" and this made Excel know that "427" was the character name, not the integer index. All data sheet names are upper case already, so "vbUpperCase" was selected because it shouldn't mess anything else up.
The first time this occurs, I see the display, and all works as planned. This is about row 8. The next time it happens (at about row 25), I get an object error on the "Set" statement. The error is that there is no worksheet with this name. I added the "On Error" statement inside the loop because I thought that maybe once the "On Error" was executed, it was no longer valid. I can not get around this error. Any clues as to what I've done wrong? Here is the relevant code which is inside a loop going through all the rows in a particular worksheet:
On Error GoTo Recover
Set destSheet = Sheets(StrConv(dataSheet.Cells(i, 1), vbUpperCase))
As I mentioned, within the first 25 rows this occurs twice. The first time I get the message, but the second I get the message on the Set that indicates there is no worksheet by that name.
FYI: The "StrConv" was added because the very first data worksheet is named "427". The "Sheets" clause somehow converted this into an integer and attempted to access "Sheets(427)". So I added the "StrConv" and this made Excel know that "427" was the character name, not the integer index. All data sheet names are upper case already, so "vbUpperCase" was selected because it shouldn't mess anything else up.