Results 1 to 5 of 5

Thread: Detect if worksheet exist, and if it does ignore the rest of the script

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Detect if worksheet exist, and if it does ignore the rest of the script

    I need my script to detect if worksheet(s) exist based on some critera and if 1 or more does, ignore the entire script.
    I want to add this part at the start of a current script I'm using. If any sheets already exist, it is causing me trouble...

    Of course this might not be the ideal way to handle this I would assume, but it simply would be best for me to include it as is and not modify the rest of my script

    The only critera that needs to be respected is if any worhseet ends with anywhere in between -1 to -1000 (could be -100, -400, -777)

    I was thinking of something like this

    If "condition is met" Then
    Exit Sub
    End If
    ' rest of the script down below

    Would this work? and if so, can someone suggest a script to determine is any sheets with my critera exist

    Thank you very much

  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Detect if worksheet exist, and if it does ignore the rest of the script

    Code:
    Sub Main()
        Dim ws
        For Each ws In Worksheets
          MsgBox i1000Check(ws.Name), , ws.Name
        Next ws
    End Sub
    
    Function i1000Check(s As String) As Boolean
      Dim i As Integer, a, v, tf As Boolean
      a = Split(s, "-")
      On Error GoTo EndFunction
      If UBound(a) = 0 Then Exit Function
      v = a(UBound(a))
      tf = v = CInt(v)
      If tf = False Then
        i1000Check = False
        Exit Function
      End If
      Select Case v
        Case 1 To 1000
          i1000Check = True
      End Select
    EndFunction:
    End Function

  3. #3
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,140
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Detect if worksheet exist, and if it does ignore the rest of the script

    So the end part of the sheet's name is something like -1, -55, -566 etc?
    Try this:

    Code:
    Dim ws As Worksheet
    Dim ary
    For Each ws In Worksheets
        ary = Split(ws.Name, "-")
        If ary(UBound(ary)) > 0 And ary(UBound(ary)) <= 1000 Then Exit Sub
    Next

  4. #4
    New Member
    Join Date
    Oct 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Detect if worksheet exist, and if it does ignore the rest of the script

    Akuini

    seems perfect!

    For my curiosity, is there any reason why the Dim ary is not define as any specific type ?
    Nonetheless it's exactly what I needed.

    Thank you

  5. #5
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,140
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Detect if worksheet exist, and if it does ignore the rest of the script

    Dim ary is the same with Dim ary as Variant

    You're welcome, glad to help, & thanks for the feedback.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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