Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Worksheet Exists
Hi All,
This is probably a simple one that I have managed to overlook but her goes. I have a workbook that contains some data sheets and repor sheets. Each report sheet is identical and extracts similar data fro the data sheet. I can set this up using formulas but it takes foreve to calculate (over 30,000 formulas). So I have set up some code to do the calculations faster. Basically, have a control list of the worksheets that are report sheets that th code should be executed on. I use a for loop to work through eac sheet and only want to perform the computations if a sheet matching th entry in the list exists. The part where I am falling over is where have set a worksheet variable with a sheet name that doesn't exist. can't find a test that works. Here is a code sample: On Error Resume Next For a = LBound(CostCentreList) To UBound(CostCentreList) CostCentreList(a) = CCRange.Cells(a + 1, 1).Value Next ......... For i = 0 To CCListLength - 1 Set ExpSheet = wkb.Worksheets(CostCentreList(i)) If IsEmpty(ExpSheet) = False Then ............ The value of ExpSheet here is 'Nothing' as I have used a sheet nam that does not exist, however the if statement says that it does. Thanks in Advance. B -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Worksheet Exists
Here's a test for a worksheet
Sub test() Dim wks As Worksheet, bln As Boolean On Error Resume Next Set wks = Worksheets("sheet1") bln = (Err.Number = 0) On Error GoTo 0 MsgBox bln End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "bcmiller " wrote in message ... Hi All, This is probably a simple one that I have managed to overlook but here goes. I have a workbook that contains some data sheets and report sheets. Each report sheet is identical and extracts similar data from the data sheet. I can set this up using formulas but it takes forever to calculate (over 30,000 formulas). So I have set up some code to do the calculations faster. Basically, I have a control list of the worksheets that are report sheets that the code should be executed on. I use a for loop to work through each sheet and only want to perform the computations if a sheet matching the entry in the list exists. The part where I am falling over is where I have set a worksheet variable with a sheet name that doesn't exist. I can't find a test that works. Here is a code sample: On Error Resume Next For a = LBound(CostCentreList) To UBound(CostCentreList) CostCentreList(a) = CCRange.Cells(a + 1, 1).Value Next ........ For i = 0 To CCListLength - 1 Set ExpSheet = wkb.Worksheets(CostCentreList(i)) If IsEmpty(ExpSheet) = False Then ........... The value of ExpSheet here is 'Nothing' as I have used a sheet name that does not exist, however the if statement says that it does. Thanks in Advance. BC --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Worksheet Exists
Hi Rob,
In the context of my code, your suggestion does not work. The sam thing is happening that the code believes there isn't a problem an keeps executing, probably because the bln error test evaluates t false. So it looks like I am still stuck. Cheers, B -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Worksheet Exists
Case Closed:
I just need to take your code to the next step and now it works. Baically, use bln in an if statement to determine whether to procee with the code execution or to generate an error report with all th items that had no corresponding sheet and could not be processed. Thanks for the assistance. Final Code: For i = 0 To CCListLength - 1 Set ExpSheet = wkb.Worksheets(CostCentreList(i)) bln = (Err.Number = 0) On Error GoTo 0 If bln = True Then Many appreciations, B -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Worksheet Exists
You've got it... nice work :)
-- Rob van Gelder - http://www.vangelder.co.nz/excel "bcmiller " wrote in message ... Case Closed: I just need to take your code to the next step and now it works. Baically, use bln in an if statement to determine whether to proceed with the code execution or to generate an error report with all the items that had no corresponding sheet and could not be processed. Thanks for the assistance. Final Code: For i = 0 To CCListLength - 1 Set ExpSheet = wkb.Worksheets(CostCentreList(i)) bln = (Err.Number = 0) On Error GoTo 0 If bln = True Then Many appreciations, BC --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test if file exists | Excel Discussion (Misc queries) | |||
Test if folder exists, create if it doesn't? | Excel Programming | |||
Test if a folder exists, create if it doesn't? | Excel Programming | |||
Test if a folder exists | Excel Programming | |||
Test to see if a worksheet exists thanks, Chip | Excel Programming |