For Each Loop Question

PaulWJ

New Member
Joined
Dec 4, 2023
Messages
12
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
HiddenSheets.PNG

"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
attempt1.PNG



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


Any corrections, or suggestions, would be welcomed. :)
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0
Solution
VBA Code:
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...
 
Upvote 0

Forum statistics

Threads
1,215,417
Messages
6,124,777
Members
449,187
Latest member
hermansoa

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