Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error? Creates 1 missing worksheet then never detects any other missing worksheets
Do I have to reset the "On Error" condition or am I doing something els wrong? I am trying to run through a series of specific worksheets amoung man worksheets. If the sheet exists then clear the entire worksheet. I the sheet does not exist then create it. It creates the first missing worksheet without any problem. When i encounters the second missing sheet it never fails the "If Not wsShee Is Nothing Then" test. Meaning it never enters the "Else" conditio again. So it does not create any of the missing sheets and crashes o "Worksheets(sSheetTo).Cells.Clear" when it tries to clear the missin worksheet. Do I have to reset the "On Error" condition or am I doing somethin else wrong? Respectfully, Craigm '=========================================== Dim wsSheet As Worksheet 'For worksheet exists only sSheetFrom = "Inventory" For k = 1 To 9 If k = 1 Then sSheetTo = "Auto" ElseIf k = 2 Then sSheetTo = "Trucks" ElseIf k = 3 Then sSheetTo = "Vans" ElseIf k = 4 Then sSheetTo = "Boats" ElseIf k = 5 Then sSheetTo = "Airplanes" ElseIf k = 6 Then sSheetTo = "Motorcycles" ElseIf k = 7 Then sSheetTo = "Trailers" ElseIf k = 8 Then sSheetTo = "SUVs" ElseIf k = 9 Then sSheetTo = "Unknown" End If ''''We need to run through this and clear all worksheets. 'If it does EXIST then clear its contents out 'If the passed in WorkSheet does not EXIST then create it. On Error Resume Next Set wsSheet = Worksheets(sSheetTo) On Error GoTo 0 If Not wsSheet Is Nothing Then'..................It does Exist wsSheet.Activate Worksheets(sSheetTo).Cells.Clear Else'............................................. ..............It doe Not exist Worksheets.Add.Name = (sSheetTo) End If Next -- Craig ----------------------------------------------------------------------- Craigm's Profile: http://www.excelforum.com/member.php...fo&userid=2438 View this thread: http://www.excelforum.com/showthread.php?threadid=39177 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error? Creates 1 missing worksheet then never detects any other missing worksheets
Hi Craig,
Just taking a guess without having to set all this up, but in the following lines of code: On Error Resume Next Set wsSheet = Worksheets(sSheetTo) On Error GoTo 0 Once your code has encountered a worksheet that exists it sets the wsSheet variable to reference that sheet. You never set this variable back to Nothing, so on subsequent loops it still maintains a reference to whatever the last worksheet it located was. Try it like this: Set wsSheet = Nothing On Error Resume Next Set wsSheet = Worksheets(sSheetTo) On Error GoTo 0 -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "Craigm" wrote in message ... Do I have to reset the "On Error" condition or am I doing something else wrong? I am trying to run through a series of specific worksheets amoung many worksheets. If the sheet exists then clear the entire worksheet. If the sheet does not exist then create it. It creates the first missing worksheet without any problem. When it encounters the second missing sheet it never fails the "If Not wsSheet Is Nothing Then" test. Meaning it never enters the "Else" condition again. So it does not create any of the missing sheets and crashes on "Worksheets(sSheetTo).Cells.Clear" when it tries to clear the missing worksheet. Do I have to reset the "On Error" condition or am I doing something else wrong? Respectfully, Craigm '=========================================== Dim wsSheet As Worksheet 'For worksheet exists only sSheetFrom = "Inventory" For k = 1 To 9 If k = 1 Then sSheetTo = "Auto" ElseIf k = 2 Then sSheetTo = "Trucks" ElseIf k = 3 Then sSheetTo = "Vans" ElseIf k = 4 Then sSheetTo = "Boats" ElseIf k = 5 Then sSheetTo = "Airplanes" ElseIf k = 6 Then sSheetTo = "Motorcycles" ElseIf k = 7 Then sSheetTo = "Trailers" ElseIf k = 8 Then sSheetTo = "SUVs" ElseIf k = 9 Then sSheetTo = "Unknown" End If ''''We need to run through this and clear all worksheets. 'If it does EXIST then clear its contents out 'If the passed in WorkSheet does not EXIST then create it. On Error Resume Next Set wsSheet = Worksheets(sSheetTo) On Error GoTo 0 If Not wsSheet Is Nothing Then'..................It does Exist wsSheet.Activate Worksheets(sSheetTo).Cells.Clear Else'............................................. ..............It does Not exist Worksheets.Add.Name = (sSheetTo) End If Next k -- Craigm ------------------------------------------------------------------------ Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381 View this thread: http://www.excelforum.com/showthread...hreadid=391774 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error? Creates 1 missing worksheet then never detects any other missing worksheets
Set wsSheet = Nothing I had to put the line in both the "If" and "Else" conditions. Now it works correctly. Thank You, Rob! -- Craigm ------------------------------------------------------------------------ Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381 View this thread: http://www.excelforum.com/showthread...hreadid=391774 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding missing data between two worksheets | Excel Discussion (Misc queries) | |||
Compare two worksheets for missing information | Excel Worksheet Functions | |||
Toolbars Missing, And option to Add Missing | Excel Discussion (Misc queries) | |||
INDIRECT.EXT problem with missing worksheets | Excel Worksheet Functions | |||
objects missing in exixting worksheets | Excel Discussion (Misc queries) |