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.
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.