VBA Hide Rows in Sheet Based on the Vlookup Value in a Named Range

Question

I have a VBA Sub that is supposed to lookup values from a Named Range and if it finds a match, hide a certain number of rows. But it's not working. In fact, it's producing results that I don't understand how it reached those results.

Sub subHideSundays()
    varMonthsArray = Range("ranMonths") 'LISTS!C11:C23
    For Each varMonth In Range("ranMonths")
        Debug.Print varMonth
        For lngRowNumber = 7 To 534 Step 17
            Debug.Print varMonth.Range("B" & lngRowNumber).Text
            If varMonth.Range("B" & lngRowNumber).Text Like "Sun*" Then
                varMonth.Rows(lngRowNumber - 1).Resize(17).Hidden = True
            Else
                varMonth.Rows(lngRowNumber - 1).Resize(17).Hidden = False
            End If
        Next
    Next
End Sub

The line "Debug.Print vbaMonth" does exactly what I expect it to do. Displays the name of the month (sheet) that contains the value which is being searched for.

But the line "Debug.Print varMonth.Range("B" & lngRowNumber).Text" which is supposed to show the Vlookup value from the month sheet (i.e., cell "JANUARY!B7") that is being searched for in the Named Range (varMonth), is actually returning the value of the cell referenced by ("B" & lngRowNumber), of the "LISTS" sheet. Which is not even included in the Named Range.

Any help would be greatly appreciated, as this is causing a stumbling block in getting the program ready for testing.

Thanks to all that reply.

Answer 1

There is no line in the code as Debug.Print vbaMonth. Do you mean Debug.Print varMonth? Anyway: you explanation is quite vague. If i have to guess you are looking for something like

Debug.Print Sheets(varMonth.Value2).Range("B" & lngRowNumber).Text

Therefore your code might be something like this:

Sub subHideSundays()
    
    Dim RngMonth As Range
    Dim LngRowNumber As Long
    Dim WksMonth As Worksheet
    
    For Each RngMonth In Range("ranMonths")
        
        Debug.Print RngMonth
        
        For LngRowNumber = 7 To 534 Step 17
                        
            Set WksMonth = Sheets(RngMonth.Value2)
            
            With WksMonth
                
                Debug.Print .Range("B" & LngRowNumber).Text
                
                If .Range("B" & LngRowNumber).Text Like "Sun*" Then
                    
                    .Rows(LngRowNumber - 1).Resize(17).Hidden = True
                    
                Else
                    
                    .Rows(LngRowNumber - 1).Resize(17).Hidden = False
                    
                End If
                
            End With
            
        Next
        
    Next
    
End Sub

In your original code your varMonth is set in the For Each Next cycle as the first (and second and third and so on) cell of the named range ranMonths. Therefore when you use varMonth you are actually referring to the cell, the actual cell containing the information you were seeking and not the information itself. You are basically telling VBA to take that cell as a starting point for your research. By setting a worksheet variable as the actual sheet using the value of the given cell, you can instruct VBA to pick the right sheet for your search.