Forum Discussion
Hide Columns dependant on Cell values
(c.Offset(9, 0).Value = 0))
Don't you want it to say?
(c.Offset(9, 0).Value <> 0))
- vonryanMay 09, 2019Brass Contributor
Hi George
You are correct and I changed this in my continued trials to get this working. After internet research i have modified my code as follows:
Sub ShowCurrentWeekOnly()
Dim c As Range, msgValue As Long
Application.EnableEvents = FalseOn Error Resume Next
For Each c In Range("B2:Q2")
msgValue = [weeknum(c.Value)]
MsgBox "Returned value is " & msgValue
If [weeknum(c.Value)] <> [weeknum(date())] Then
c.EntireColumn.Hidden = True
ElseIf [weeknum(c.Value)] = [Weeknum(Date)] Then
c.EntireColumn.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = TrueEnd Sub
I use the message box to return the values i am trying to analyse.
When I use msgValue = c.Value the message box returns the date in excel format.
When I use msgValue = [weeknum(c.Value)] the message box returns 0.
I know the issue is here somewhere but I cannot get the function [weeknum(c.Value)] to return a week number. if I use the formula =weeknum(B2) direct in the worksheet it return the week number associated with the cell date.
The internet led me to believe that [weeknum(c.Value)] is the same as evaluate("=weeknum(c.Value)")
Your help would be gratefully appreciated.
Vonryan