Run-time error '13' after several succes runs

[kerekesm]

Hello everyone,

My VBA code starts like this:

Sub Refresh()

Dim LastRunWeek As Long, LastRunDate As Date
Dim CurrentWeek As Long, CurrentYear As Long

LastRunDate = Sheets(1).Range("M8")
LastRunWeek = Application.WorksheetFunction.WeekNum(LastRunDate)
CurrentWeek = Application.WorksheetFunction.WeekNum(Date)

If CurrentWeek = LastRunWeek Then

The purpose of this section is to check the date between today and the date the macro was last run (Cell M8). And it worked perfectly on my computer. But after my boss opened it, run it and renamed it (the file remained in the same folder) I can't run the macro anymore. I get the following error: Run-time error '13': Type mismatch. When I click on debug, it has problem with this line:


LastRunDate = Sheets(1).Range("M8")

Maybe my boss and I have different version of Excel, could this cause the problem? Possibly I made some mistakes in the declaration phase. It is really annoying that it worked and now it doesn't.
Can anyone help me in this?

Thank you in advance,
Mark

[kerekesm]

Since then, I found out that there is a problem with the dates.
Normally this is the last line of the macro:

Sheets(1).Range("M8").Value = Format(Now, "yyyy/mm/dd/")
The result is: 2020.11.30. in cell M8.
And without the dot the macro is alive again.
In this case my question is, what sould I change in the last line to get a date without "."

[p45cal]

The Format part normally returns text type data, not date type data, then you're relying on Excel at the sheet level to interpret this correctly.
Instead, format the cell, not the data going into it so:

Sheets(1).Range("M8").numberformat = "yyyy/mm/dd"
(without that final forward slash in your last message btw, which certainly wouldn't be recognised as a date by Excel)
and just put a plain date in:


Sheets(1).Range("M8").Value = Now
which will include time data too, or a more usual:

Sheets(1).Range("M8").Value = Date

On a completely separate note, using Sheets(1) in the code could be a little haphazard; it refers to the first sheet in the file, and because users can change the order of sheets the code might not always refer to the sheet you expect!

[kerekesm]

Thank you p45cal for your help, this was a new information to me. Also thank you for the Sheets tip, I will change my referring style.
Have a nice day!