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

vlacombe

New Member
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
 

Kenneth Hobson

Well-known Member
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
 

Akuini

Well-known Member
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
 

vlacombe

New Member
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 :)
 

Akuini

Well-known Member
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

This Week's Hot Topics

Top