Type mismatch error

[Jay]

I have two workbooks that have been set up with the same layout and formating
etc. When I run a macro that contains the following code (see below), pointed
to one workbook, everything runs fine. But when I point to the other
workbook, I get a Run-time error '13': Type mismatch.

Code from macro:
For Each cell In TimeSrcRng
If cell.Value <> "" Then
If cell.Value >= dtStart And cell.Offset(0, 1).Value <= dtEnd Then
cell1 = cell.Offset(0, -2).Value
Set cell1 = cell1.Offset(1, 0)
End If
End If
Next

The error occures on line:
If cell.Value >= dtStart And cell.Offset(0, 1).Value <= dtEnd Then

How can I figure out what's wrong with the second workbook?

 

[Jim Thomlinson]

AS a guess you are looking for dates that fall between a start and end date?
Are all of the cel values dates that you would be camparing... Try adding an
isdate value check to your code to determine the nature of the cell value...

For Each cell In TimeSrcRng
if not isdate(cell.value) then msgbox cell.address '***
If cell.Value <> "" Then
If cell.Value >= dtStart And cell.Offset(0, 1).Value <= dtEnd Then
cell1 = cell.Offset(0, -2).Value
Set cell1 = cell1.Offset(1, 0)
End If
End If
Next

 

[Jay]

Yes, I am looking for dates that fall between a start and end date, and yes,
all cel values are dates.

I did add the isdate check, and it checks out - they are all dates.
There are a lot of blank spaces in the range so I modified the check...

If cell.Value <> "" Then
If Not IsDate(cell.Value) Then MsgBox cell.Address '***
End If

But it found no cells that were not dates. I'm a little mysified - don't
know why one workbook works but I can't run it on the other.

Could it be something else in the code that I'm just not seeing?

Here's the full code:

Sub Weekly_Report()

Const strSourceWksName As String = "Time Check Log"
Const strDestinationWbkName As String = "DraftingWeeklyActivityReport.xls"
Const strDestinationWksName As String = "Sheet1"

Dim dtStart As Date, dtEnd As Date
Dim TimeSrcRng As Range
Dim cell1 As Range
Dim wksDestination As Worksheet
Dim wbkSource As Workbook
Dim wksSource As Worksheet
Dim cell As Range

' set wbkSource based on wheather drafter is "Jay" or "Dave"
If Range("I2") = "" Then
MsgBox "Enter Person Reporting in Cell I2"
Exit Sub
ElseIf Range("I2") = "Jay" Then
Set wbkSource = Workbooks.Open("H:\FAC\JayProjTimeTracking.xls",
UpdateLinks:=False, ReadOnly:=True)
ElseIf Range("I2") = "Dave" Then
Set wbkSource = Workbooks.Open("H:\FAC\Dave
Sipes\DavProjTimeTracking.xls", UpdateLinks:=False, ReadOnly:=True)
Else
MsgBox "Person Reporting name mispelled (or is in all caps)"
Exit Sub
End If

' set begining cell of range for full list of work orders
Workbooks(strDestinationWbkName).Activate
Set cell1 = ActiveSheet.Range("M6")

' set TimeSrcRng variable
wbkSource.Activate
Set TimeSrcRng = Nothing
On Error Resume Next
Set TimeSrcRng = Range("C3:C3000")
On Error GoTo 0

If TimeSrcRng Is Nothing Then
MsgBox "Something wrong with source range!"
Exit Sub
End If


' define start and end dates
Workbooks(strDestinationWbkName).Activate
dtStart =
DateValue(ThisWorkbook.Sheets(strDestinationWksName).Range("E1"))
dtEnd = DateValue(ThisWorkbook.Sheets(strDestinationWksName).Range("G1"))

' generate full list of work orders worked on during specified week
For Each cell In TimeSrcRng
If cell.Value <> "" Then
If cell.Value >= dtStart And cell.Offset(0, 1).Value <= dtEnd Then
cell1 = cell.Offset(0, -2).Value
Set cell1 = cell1.Offset(1, 0)
End If
End If
Next

' copy unique Work Order numbers to columb "B"
Range("M6:M200").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"B6"), Unique:=True

End Sub

Source Workbook (Jay or Dave) layout looks like this:
A B C D
E
2 Work Order No. (blank) Time In Time Out Time Elapsed
3 7664 11/3/2008 11:00 11/3/2008 11:30
0:30
4 7664 11/3/2008 12:01 11/3/2008 14:13
2:12
5
6 7723 11/5/2008 8:25 11/5/2008 11:14
2:49
7 7723 11/5/2008 11:14 11/5/2008 12:33
1:18

 

[Jay]

OK, I messed around with it long enough to discover the problem was in a cell
that appeared to be blank but for some reason was being read as having
something in it (other than a date).

I don't know why it didn't work before, but after running it again, your
isdate value check did find this offending cell.

Thanks for your help!