For Each Loop Question

[PaulWJ]

Part of a Workbook I have is trying to indicate which months (the sheetlist) are visible and have no open cases. I also need to know which sheets are hidden already, or have Open cases. There are other worksheets in the Workbook than those listed.

I have a list of worksheets on a different worksheet which has been set a defined name of "Hidden_Sheetlist". Some of these are Hidden, and the visibility status can change. I'd like a loop that checks whether each worksheet on the list is visible (AND if Open Cases=0) and, if so, enters a "1" (for example) in the corresponding column (which is 2 columns over). If it's not visible (OR if Open cases >0), a "0" is entered in the third column.

"Hidden_Sheetlist" is the first column
"Open_Cases_Total" is the second
"Open_Cases_Output" is the third
"Hid_top_Row" is a formula that tells me what the first row in the named range is
"Hid_Sheets_List" is the name for all three columns (AK22:AM34)

I've tried a few different things, none of which have been particularly successful. Closest 2 attempts I got is shown below, but I got a Type Mismatch error - so suspect I wasn't actually that close

The highlighted section is where I got the Type Mismatch (which, I reckon, is to do with the way I've defined 'ws')

Any corrections, or suggestions, would be welcomed.

 

[Micron]

You've declared ws as a Worksheet, which is an object. So your ranges can't contain objects thus you get that error. You have sheet names in a list, so you will need to refer to the sheets by name.
My other suggestion would be to post code within code tags (use vba button on posting toolbar) rather than pictures of code. If your query as about a code error, identify the failing line with a comment. I don't think anyone really wants to re-type what they see in order to test failing code where the failure is not obvious.

 

[kevin9999]

Please try the following on a copy of your workbook. Make sure you change the name of the sheet that contains the Hidden_Sheetlist range where indicated in the code. In addition to @Micron 's advice re code tags, could you also post your sheet using the XL2BB add in.

Option Explicit
Sub hidden1()
    Dim ws As Worksheet, c As Range, s As String
    For Each c In Worksheets("Sheet1").Range("Hidden_Sheetlist")    '<-- *** Change to actual sheet name ***
        s = c
        If Worksheets(s).Visible = True And c.Offset(, 1) = 0 Then c.Offset(, 2) = 1 Else c.Offset(, 2) = 0
    Next c
End Sub

 

[PaulWJ]

Thanks both. I'll give that a go and see how I get on.

 

[PaulWJ]

Thanks both. I'll give that a go and see how I get on.

Smashing Kevin. That worked a treat. Appreciate it.

Micron, I'll take your comments on board for my next post.

 

[Micron]

Option Explicit
Sub hidden1()
    Dim ws As Worksheet, c As Range, s As String
    For Each c In Worksheets("Sheet1").Range("Hidden_Sheetlist")    '<-- *** Change to actual sheet name ***
        s = c
        If Worksheets(s).Visible = True And c.Offset(, 1) = 0 Then c.Offset(, 2) = 1 Else c.Offset(, 2) = 0
    Next c
End Sub

Why declare ws and then not use it? At least after reviewing 3 times I can't see it...

 

[kevin9999]

Yes, good point. The perils of using templates.

 

[kevin9999]

Smashing Kevin. That worked a treat. Appreciate it.

Micron, I'll take your comments on board for my next post.

Glad we could help, and thanks for the feedback