Runtime Error 13 - Type Mis-match

[Kim Owens]

Hi, I inherited this macro. Since we have migrated to XP and Excel 2003, we
are getting a Runtime Error 13 - Type Mis-match on the following line of
code.

dat = Replace(Sheets(Worksheets.Count - 2).Name, ".", "/")

Does anyone have any ideas on how to fix it? It looks like it should be a
string.

Thanks in advance.
-Kim

-----------------------------------
Sub DBandReportPageSetup()
' Macro1 Macro

' DataBase and Reports page setup
' This Macro also sort Database and Reports data

'---------------------------------------- Declaration of
Variables------------------------------------------
Dim totcol, totlin, x, y, totlin2, totlinrp As Integer
Dim reportname As String
Dim dat As Date

'----------------------------------------- Database Page Setup
---------------------------------------------

' Application.DisplayAlerts = True

reportname = Sheets(Worksheets.Count - 1).Name
dat = Replace(Sheets(Worksheets.Count - 2).Name, ".", "/")


y = 0



If ActiveSheet.Name <> reportname Then Sheets(reportname).Activate
totlinrp = Range("A65536").End(xlUp).Row
If ActiveSheet.Name <> "DataBase" Then Sheets("DataBase").Activate
totlin = Range("B65536").End(xlUp).Row
totcol = Cells(2, Columns.Count).End(xlToLeft).Column
totlin2 = totlin + 1

Range(Cells(3, 1), Cells(totlin, totcol)).Select
Selection.Sort Key1:=Range("C3"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = 2
Selection.Font.FontStyle = "Regular"
Selection.Font.ColorIndex = xlAutomatic

For x = 3 To totlin
y = y + 1
Cells(x, 10).Value = UCase(Trim(Cells(x, 10).Value))
If Cells(x, 3).Value = dat Then
For z = 3 To totlinrp
If Cells(x, 1).Value = Sheets(reportname).Cells(z, 1).Value And
Sheets(reportname).Cells(z, 10).Value = 0 Then GoTo notred:
Next
Rows("3:3").Select
Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Range(Cells(x + 1, 1), Cells(x + 1, 38)).Copy
Cells(3, 1).Select
ActiveSheet.Paste
Range(Cells(3, 1), Cells(3, 38)).Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
Range(Cells(x + 1, 1), Cells(x + 1, 38)).Select
Selection.Delete shift:=xlUp
End If
notred:

If Cells(x, 10).Value = "YES" And Cells(x, 3).Value <> dat Or Cells(x,
10).Value = "ACCEPTABLE" Then
Range(Cells(x, 1), Cells(x, 38)).Copy
Cells(totlin2, 1).Select
ActiveSheet.Paste
Range(Cells(x, 1), Cells(x, 38)).Select
Selection.Delete shift:=xlUp
totlin = totlin - 1
x = x - 1
End If
If y = totlin2 - 1 Then GoTo suite:
Next
suite:
y = 0
For x = 3 To totlin
y = y + 1
If Cells(x, 2).Value = dat Then
Rows("3:3").Select
Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Range(Cells(x + 1, 1), Cells(x + 1, 38)).Copy
Cells(3, 1).Select
ActiveSheet.Paste
Range(Cells(3, 1), Cells(3, 38)).Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
Range(Cells(x + 1, 1), Cells(x + 1, 38)).Select
Selection.Delete shift:=xlUp
End If
If y = totlin2 - 1 Then GoTo suite2:
Next
suite2:

totlin = Range("B65536").End(xlUp).Row
totcol = Cells(2, Columns.Count).End(xlToLeft).Column
Range(Cells(3, 1), Cells(totlin, totcol)).Select
Selection.VerticalAlignment = xlTop

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range(Cells(3, 9), Cells(totlin, 9)).Select
Selection.WrapText = True
Range(Cells(3, 1), Cells(totlin, 1)).Select
Selection.HorizontalAlignment = xlLeft
Range(Cells(3, 2), Cells(totlin, 8)).Select
Selection.HorizontalAlignment = xlCenter
Selection.Font.Size = 9
Range(Cells(3, 10), Cells(totlin, 11)).Select
Selection.HorizontalAlignment = xlCenter
Selection.Font.Size = 9
Range(Cells(3, 9), Cells(totlin, 9)).Select
Selection.HorizontalAlignment = xlLeft
Selection.Font.Size = 7
Range(Cells(3, 12), Cells(totlin, 38)).Select
Selection.HorizontalAlignment = xlLeft
Selection.Font.Size = 7
Cells(3, 1).Activate

 

[JLGWhiz]

You have Dat declared as a date but a sheet name is a string. Dim Dat as
String and it should work.

 

[jaf]

Hi Kim,
"dat" is dim'd as data type date.
The sheet has to be named with a date like name.
Since "/" is an illegal char in a sheet name the workbook was set up to use a period in it's place.
The macro is changing the return string "dat", not the sheetname, back to "/" to be used elsewhere.

So the sheets have to be named 06.02.2009 or 06.02.09 or the macro will trigger the error you saw.

You may want to add an error check to ensure the users use the correct sheetnames and avoid a crash.

John

 

[ryguy7272]

Try:

On Error Resume Next


HTH,
Ryan---

 

[Patrick Molloy]

try replacing this

dat = Replace(Sheets(Worksheets.Count - 2).Name, ".", "/")

with this:
dim sDate as string
sDate = Replace(Sheets(Worksheets.Count - 2).Name, ".", "/")
if not isdate(sDate) then
msgbox "Sheet name is not a valid date: " & sDate
exit sub
end if
dat = cdate(sDate)