How to use a "with" with worksheets()

[Shelly]

I have been trying to get this sub to work, but with no luck. It takes "Index As String" to
represent either the sheet name or the Index, but it always dies with a "subscript out of range"
error when called by a loop such as:

For i = 2 To 4 Step 1
If Worksheets(i).Visible <> xlHidden Then
Reset_Sheets (i)
End If
Next

When the sub is called by itself as in:
Reset_Sheets ("System_2")
it works fine. I have checked the values passed and the are correct either when I pass a number or
the sheet name. I am really perplexed by this.

The problem sub is:

With Worksheets(Index) <-- subscript out of range error stops here

.Name = Index ' this returns the sheet name to the default

For Each c In .Range("C6:F12")
c.Value = 0
Next

For Each c In .Range("C15:F19")
c.Value = 0
Next

....blah, blah, blah...

.Visible = xlSheetHidden

End With

Thank you for any advice you can give me,

Shelly

 

[Frank Kabel]

Hi
try
With Worksheets(CInt(Index))

 

[Tom Ogilvy]

Dim idex as Variant
if isnumeric(index) then
idex =clng(index)
Else
idex = Index
End if
With Worksheets(idex)

however, this line makes no sense:

..Name = Index ' this returns the sheet name to the default

If Index contains a valid sheet name, then you are not renaming the sheet
since it already has that name (you used it to reference the sheet).

If Index contains a number, then you are renaming the sheet to the Number.

 

[Tom Ogilvy]

When the sub is called by itself as in:

Reset_Sheets ("System_2")

Cint("System_2")

will be problematic

 

[Frank Kabel]

Hi Tom
you're right. I misread the question. thought only an index value would
be used. Your solution is quite more robust

 

[Tom Ogilvy]

Well it does assume the sheet won't have a name like "1" which wouldn't be
true if she keeps her code the way it is.

 

[Shelly]

Dim idex as Variant
if isnumeric(index) then
idex =clng(index)
Else
idex = Index
End if
With Worksheets(idex)

however, this line makes no sense:

.Name = Index ' this returns the sheet name to the default

If Index contains a valid sheet name, then you are not renaming the sheet
since it already has that name (you used it to reference the sheet).

If Index contains a number, then you are renaming the sheet to the Number.

The sheets can be changed by the user, and so the original name would still refer to the sheet,
Correct? That line is supposed to return the sheet to it's default name. I checked that even if
the user has changed the name it still uses the original...but for how long???


Thanks,

Shelly

 

[Tom Ogilvy]

The argument to Worksheets is the current sheetname or the sequence number.
So if the user has renamed the sheet, you need to use that name to refer to
the sheet.

It sounds like you want to use the codename of the worksheet. Chip Pearson
describes the codename concept on this page:

http://www.cpearson.com/excel/codemods.htm

It is a large scroll or two down the page.

Back to your original question. If you stored the name of the sheet in a
variable, then that variable will hold that value for its lifetime unless
you set it to something else. However, in the meantime if the the user
renames the sheet, this old original name will not be useful in trying to
reference the sheet. You will get a subscript out of range error because
that name no longer exists.