Vba Code alteration reqd.

[Ian Betteridge]

Good Evening,
I have some code which was kindly supplied via this forum.

Private Sub Workbook_Open()
If Month(Date) > 1 Or Day(Date) >= 7 Then
With Sheets("wkssystemA2").Range("B64").Resize(1, Month(Date) - IIf(Day(Date) < 7, 1, 0))
.Value = .Value
End With
End If
End Sub

I now want this code to apply to all cells in the range CB3:CM59 and not cell B64.
Is this possible? If yes, what will the code become?
Regards Ian

 

[boblarson]

How about:

With Sheets("wkssystemA2").Range("CB3:CM59").Resize(1, Month(Date) - IIf(Day(Date) < 7, 1, 0))

 

[VoG]

Maybe like this

Private Sub Workbook_Open()
Dim c As Range
If Month(Date) > 1 Or Day(Date) >= 7 Then
    For Each c In Sheets("wkssystemA2").Range("CB31:CM59")
        With c.Resize(1, Month(Date) - IIf(Day(Date) < 7, 1, 0))
            .Value = .Value
        End With
    Next c
End If
End Sub

 

[Ian Betteridge]

I thought that was obvious, so I tried it before posting and it didn't work. That's where I got lost.
With regards to Bobs post.

 

[Ian Betteridge]

Hi Peter,
I've tried that and still can't get a result. I'd expect the cells CB3:CF59 to have changed from a formula to a number after closing down and then re-opening the workbook.
? Probably me but can't see where.

 

[VoG]

Did the original code work? I've only changed the range.

 

[Jonmo1]

This is probably not the problem, but this
For Each c In Sheets("wkssystemA2").Range("CB31:CM59")

probably only needs to be
For Each c In Sheets("wkssystemA2").Range("CB31:CB59")


Also, as Vog said this is all assuming the code you posted DOES actually work for the single cell.