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

vlacombe

New Member
Joined
Oct 4, 2019
Messages
31
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
Dim ary is the same with Dim ary as Variant

You're welcome, glad to help, & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top