Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    193
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi,

    This code is calculating and formatting the difference between two dates in actual days and working days:

    The code below works fine in Xl2K but in Excel 07 it gets the following.

    Type 13 Mismatch Error

    Code:
    Public Sub ShowStatusCell()
    Dim TempDate As Date
    Dim Ofst As Integer
    
    If Not Application.Intersect(ActiveCell, Range("CalRng")) Is Nothing Then
    	If Len(ActiveCell.Value) > 0 Then
        	Ofst = ActiveCellMonthOffset()
        	TempDate = DateSerial(Range("TheYear"), Range("TheMonth") + Ofst, Val(Trim(Left(ActiveCell.Value, 2))))
    
        	If Application.International(xlMDY) = True Then
    ' 		Check if date format is m/d/y
          	Range("StatusCell").Value = Format(TempDate, "mmm d, yyyy") & _
            	"  " & Format(DayOfYear(TempDate), "##0") & _
            	NumberSuffix(DayOfYear(TempDate)) & " day of year." & _
            	"  Days From Now: " & _
            	Format(DateDiff("d", Now(), TempDate), "##,##0") & _
            	"  (Workdays: " & _
            	Format(Evaluate("NETWORKDAYS(" & """" & _
            	Format(Now(), "mm/dd/yy") & """" & "," & """" & _
            	Format(TempDate, "mm/dd/yy") & """" & _
            	",PubHols)"), "##,##0") & ")"
    '            Format(TempDate, "mm/dd/yy") & """" & ")")) & ")"
    
        	Else
    ' 		Date Format is d/m/y
          	Range("StatusCell").Value = Format(TempDate, "mmm d, yyyy") & _
            	"  " & Format(DayOfYear(TempDate), "##0") & _
            	NumberSuffix(DayOfYear(TempDate)) & " day of year." & _
            	"  Days From Now: " & _
            	Format(DateDiff("d", Now(), TempDate), "##,##0") & _
            	"  (Workdays: " & _
            	Format(Evaluate("NETWORKDAYS(" & """" & _
            	Format(Now(), "dd/mm/yy") & """" & "," & """" & _
            	Format(TempDate, "dd/mm/yy") & """" & _
            	",PubHols)"), "##,##0") & ")"                            'Error on this line<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        	End If
    	Else
        	Range("Statuscell").Value = ""
    	End If
    
    Else
    	Range("StatusCell").Value = ""
    End If
    
    End Sub

    Please note most of the first part of the code is not executed becasue the Regional Setting for Date in Aust are "d/m/y" - xlMDY is false.

    Also PubHols is a Named Range in the s'sheet which may need to be defined.

    Any help would be greatly appreciated.

    Thanks in advance,

    Peter Moran

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    I'm not able to help with Excel coding, but to gather information for others: Assuming you debug the code or run it from the VB Editor, which line is highlighted as causing the error?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    193
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Jefferson,

    Have updated the code with where the error occurs.

    Was there originally but lost in an update.

    Regards,

    Peter moran

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'd step through the code in 2007. Then when you get to the offending line, highlight parts of that line and try to locate what is causing the error.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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