Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012

    Unanswered: Excel VBA Type Mismatch when Declaring the Current Month's 2-Digit Number

    I have a workbook with macros that filter and format some data.

    I want to add a macro that will also move the final output sheet of this macro to a new non macro-enabled workbook with multiple tabs. One for each day of the current month.

    I need to declare variables for month and day so that it opens the correct file no matter what month it is. The naming convention for my files is Machines on Demo YYMM - MMMM YYYY
    This means I need 4 variables YY, MM, MMMM, and YYYY
    Here is the code:

    Sub mcrDemoMoveSheet()
        Dim vCurrentSheetName As String
        Dim vMonth2 As String, vYear2 As String
        Dim vMonthName As String, vYear4 As String
        Dim vNoOfSheets As Integer
         vCurrentSheetName = ActiveSheet.Name
    'The below row is highlighted when I press Debug on the error dialog box.
         vMonth2 = Format(Evaluate("=WORKDAY(TODAY()-1)"), "mm")
         vYear2 = Format(Evaluate("=WORKDAY(TODAY()-1)"), "yy")
         vMonthName = Format(Evaluate("=WORKDAY(TODAY(),-1)"), "mmmm")
         vYear4 = Format(Evaluate("=WORKDAY(TODAY(),-1)"), "yyyy")
        Workbooks.Open Filename:= _
            "Z:\Regular Reports\Machines on Demo\Machines on Demo" & vYear2 & vMonth2 & " - " & vMonthName & " " & vYear4 & ".xlsx"
        vNoOfSheets = Sheets.Count
        Windows("Machines on Demo - Macros.xlsm").Activate
        Sheets("" & vCurrentSheetName & "").Move After:=Workbooks( _
            "Machines on Demo" & vYear2 & vMonth2 & " - " & vMonthName & " " & vYear4 & ".xlsx").Sheets(vNoOfSheets)
        Windows("Machines on Demo - Macros.xlsm").Activate
    End Sub
    When I run this code, it returns a Run-time error '13': Type mismatch. and highlights the "vMonth2 = ..." line.
    I've tried declaring vMonth2 as a Variant, but the result is the same.

  2. #2
    Join Date
    Sep 2008
    London, UK

    My guess is you're using XL 2003 or earlier where WORKDAY() was part of the analysis toolpak, the formula you're evaluating is returning an error value, so you get a type mismatch error. VBA has it's own date functions which will be much faster to use within VBA than evaluating Excel's worksheet functions such as WORKDAY(). Something like this:
    Sub Main()
        Dim dteLastWorkDay As Date
        dteLastWorkDay = DateAdd("d", IIf(Weekday(Date) = vbMonday, -3, -1), Date)
        Debug.Print Format(dteLastWorkDay, "dd")
        Debug.Print Format(dteLastWorkDay, "mm")
        Debug.Print Format(dteLastWorkDay, "yy")
    End Sub

Posting Permissions

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