Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch error
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch error
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 -- HTH... Jim Thomlinson "Jay" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch error
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(strDestinationWksNam e).Range("E1")) dtEnd = DateValue(ThisWorkbook.Sheets(strDestinationWksNam e).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 "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Jay" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch error
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! "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Jay" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
runtime error 13 - type mismatch error in Excel 97 on Citrix | Excel Programming | |||
Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming |