Consulting

Results 1 to 4 of 4

Thread: Run-time error '13' after several succes runs

  1. #1
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    3
    Location

    Run-time error '13' after several succes runs

    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

  2. #2
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    3
    Location
    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 "."

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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!
    Last edited by p45cal; 11-30-2020 at 07:28 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    3
    Location
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •