Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Error Trapping (Excel 97)

    Wasn't sure what the subject of this thread should have been!

    I have the following code:

    Dim ws As Worksheet
    For Each ws In Worksheets(Array("Smith Pipe", "Smith Fcst", "Cole Pipe", "Cole Fcst", "Flock Pipe", "Flock Fcst"))
    ws.Activate
    With ActiveSheet

    <Rest of irrelavant code here>

    End With
    Next ws

    The worksheets are created from another set of code based on the data in my spreadsheet. The problem is that sometimes I do not have all 6 of those spreadsheets. Sometimes I will only have "Smith Pipe", "Flock Fcst" & "Cole Pipe" in my workbook.

    So what occurs is that I get an error when it goes to the worksheets that I have named in the above code.

    Is there someway to say - If worksheet does not exist go on to next one in array?

    Thanks!

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Error Trapping (Excel 97)

    Hi awckie,

    You could use:
    On Error Resume Next
    before the For ... line, so that the routine skips over any missing sheets, but that won't help if a user has re-named the sheet. So you might need to re-think the approach by using a routine that goes though all sheets and tests for an attribute (perhaps in a protected cell). Some code I got for doing something along those lines (possibly from another posting here in the Lounge) is:

    Sub MultiSheetsSelect()
    Dim SheetArray() As Variant
    Dim ws As Worksheet
    Dim indx As Integer
    For Each ws In ThisWorkbook.Sheets
    If ws.Range("A1") = "x" Then
    ReDim Preserve SheetArray(indx)
    SheetArray(indx) = ws.Index
    indx = indx + 1
    End If
    Next
    If indx > 0 Then
    Sheets(SheetArray()).Select
    End If
    End Sub

    The above example selects all sheets with an "x" in cell A1.


    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Error Trapping (Excel 97)

    The things you learn everyday [img]/forums/images/smilies/smile.gif[/img]

    I added the On Error Resume Next before the For Line......

    Sub TBCO_FormatHCDetailSheets()

    Application.StatusBar = "Creating Head Coach Detail"

    Sheets("Smith Pipe").Select

    Dim ws As Worksheet
    On Error Resume Next
    For Each ws In Worksheets(Array("Smith Pipe", "Smith Fcst", "Cole Pipe", "Cole Fcst", "Flock Pipe", "Flock Fcst"))
    ws.Activate
    With ActiveSheet
    <Irrelevant Code Here>
    End With
    Next ws

    End Sub


    What is happening now is that I have the following worksheets on this batch - Smith Fcst, Smith Pipe, Flock Fcst, Flock Pipe. What happens is that it performs all the code on the Smith Fcst worksheet, but then completely ends the subroutine and moves on to the next subroutine. So it's not really moving on to the next worksheet.

    What do you think is happening?

    (I don't have to worry about the user changing the name of the worksheets, as the worksheets are created automatically right before this sub is performed with no available user intervention - Well I'm sure there probably is a way for user intervention, but believe me - my 2 coworkers who are using this will just yell for me if there is an issue [img]/forums/images/smilies/smile.gif[/img]

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Error Trapping (Excel 97)

    Is the sequence important? If not, you could loop through all the Sheets (as shown in Macropod's code) and check whether the current sheet is on the list, e.g.,

    If InStr(1, "|Smith Pipe|Smith Fcst|Cole Pipe|Cole Fcst|Flock Pipe|Flock Fcst|", "|" & ws.Name & "|") > 0 Then

    Please note that I just typed that out there, and didn't check that ws.Name actually is the correct property, but you get the idea. Also, you might need to set the compare type to Text (otherwise it is case-sensitive).

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Error Trapping (Excel 97)

    On Error Resume Next skips the entire line if any part of the line is invalid. A more tedious way to handle this is:

    Dim ws As Worksheet
    Dim wsArr As Variant
    Dim intC As Integer
    wsArr = Array("Smith Pipe", "Smith Fcst", "Cole Pipe", "Cole Fcst", "Flock Pipe", "Flock Fcst")
    For intC = 0 To UBound(wsArr)
    On Error Resume Next
    Set ws = Worksheets(wsArr(intC))
    If Not ws Is Nothing Then
    ws.Activate
    With ActiveSheet
    ' <Code Here>
    End With
    End If
    Set ws = Nothing
    Next intC

    I'll have to check into JScher2000's approach, it's interesting.

    BTW, are you sure you need to Activate the 'ws'? There are very few times Activate is needed; some print settings and View setting of the ws require it, but otherwise you can run code on the sheet without Activating it, and it speeds things up a bit. Try just using:

    If Not ws Is Nothing Then
    With ws
    ' <Code Here>
    End With
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Trapping (Excel 97)

    Note that you needn't activate the sheet to work on it:

    WS.activate
    With Activesheet

    can be replaced with:

    With WS

    It also speeds up your code.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •