macro doesn't work in MAY when it has to find range MONTH

[still learning]

Hi

I have a macro that is part of “Private Sub Workbook_Open()” which finds the current month then goes to the current day. Then it scrolls the page so the range (month) is at the top left.

Private Sub Workbook_Open()
gotodatemonth
gotodateday
Application.Goto Reference:=Range(Format(Date, "mmmm")), Scroll:=True
End Sub

Sub gotodatemonth()
Dim C As Range
Set C = Cells.Find(Month)
‘The line>>>Set C = Cells.find(Month)<<<< causes an error >>“argument not optional”
If Not C Is Nothing Then C.Select
End Sub

Sub gotodateday()
Dim C As Range
Set C = Range("B1:O1300").Find(Date)
If Not C Is Nothing Then C.Select
End Sub

If I change>>>> Set C = Cells…………
To

Application.Goto Reference:="may"

The macro works.
I can’t remember if this happens every may.
It’s not hard to change once a year, but I wonder if there is a better way
I think it gives an error because MAY is also a column


mike

 

[rlv01]

"Month" is a vba function that requires a date argument, so it would need to be something like Set C = Cells.Find(Month(Now)). But the Month function returns an integer, not a string with the month name (e.g. "May") so you would have to write some code to return the month name:

Sub gotodatemonth()
    Dim C As Range
    Dim MonthStr As String

    Select Case Month(Now)
    Case 1
        MonthStr = "January"
    Case 2
        MonthStr = "February"
    Case 3
        MonthStr = "March"
    Case 4
        MonthStr = "April"
    Case 5
        MonthStr = "May"
    Case 6
        MonthStr = "June"
    Case 7
        MonthStr = "July"
    Case 8
        MonthStr = "August"
    Case 9
        MonthStr = "September"
    Case 10
        MonthStr = "October"
    Case 11
        MonthStr = "November"
    Case 12
        MonthStr = "December"
    End Select

Set C = Cells.Find(MonthStr)
If Not C Is Nothing Then C.Select
End Sub

 

[still learning]

Hi rlv01
That code for month works perfect.
Thank you.
I added the macro >>gotodateday() and the .....scroll :=true just before "End sub"
And it works just the way I need it to.
Now I won't have to change the macro every MAY

Thank you, again

mike