none
Runtime error '13' - type mismatch problem! Again...

    Question

  • Hey all

    Thanks to the help of the forums here i thought i had most of my code working however, this infamous 'runtime error 13' keeps cropping its head up on only 2 of my 10 sub routines! :( Ive spent most of my work day searching through forums and google but i am no closer to finding a solution.

    The 2 functions that it is affecting are important and i need to get them sorted else i may as well scrap my two days work!

    Ive posted the code below and hopefully if anyone has a chance they could have a look and see if they notice anything that may be the cause.

    Ive highlighted the lines of code that are being thrown up in the debugger.

    '***********************************************
    ' Calculate Award
    '***********************************************

    Sub cmdCalculate_Award_Click()

        Dim x, z As Integer
        Dim cell, cell2 As String
       
        'Take Z as holding the value of the total number of employees being processed,
        'i.e. the last field containing data in the spreadsheet
       
        z = InputBox(Prompt:="Please enter the total number of employees to be processed this quarter.", _
            Title:="Maximum No of Employees", Default:="Type number here:")
       
        For x = 2 To z 'z = number of employees being processed
       
        cell = "I" & x  'I signifies the date they joined the company
        cell2 = "F" & x 'F signifies the Award Due
       
        '--------------------------------------------------------------------------------------
        'Amend the date below before running the report each quarter as they may need updated
       
           
    If (DatePart("yyyy", "" & Range(cell)) = 2001) Then
                Range(cell2) = 5
            ElseIf (DatePart("yyyy", "" & Range(cell)) = 1996) Then
                Range(cell2) = 10
            ElseIf (DatePart("yyyy", "" & Range(cell)) = 1991) Then
                Range(cell2) = 15
            ElseIf (DatePart("yyyy", "" & Range(cell)) = 1986) Then
                Range(cell2) = 20
            ElseIf (DatePart("yyyy", "" & Range(cell)) = 1981) Then
                Range(cell2) = 25
            ElseIf (DatePart("yyyy", "" & Range(cell)) = 1976) Then
                Range(cell2) = 30
            End If
           
        Next
       
        'Popup box to notify user of changes - available for customisation
        MsgBox "The awards due have been automatically updated."

    End Sub

    '****************************************************************************************

    '***************************************************
    'Generate the managers email dispatch date
    '***************************************************

    Sub cmdMGR_email_Dispatch_date_Click()

        Dim x, z As Integer
        Dim cell, cell2 As String
           
        z = InputBox(Prompt:="Please enter the total number of employees to be processed this quarter.", _
            Title:="Maximum No of Employees", Default:="Type number here:")
       
        cell = "L " & x      'cell refers to Employee Dispatch Date
        cell2 = "Y " & x    'cell2 refers to Manager Dispatch date which is 1 week earlier than EE date
       
        For x = 2 To z
           
            cell2 = cell - 7

        Next

        MsgBox "The Managers Email Dispatch date has now been updated."

    End Sub

    ****************************************************************************************

    Any help is greatly appreciated.

    Scott

    Thursday, June 22, 2006 2:15 PM

Answers

  • Hi,

    cell2 = cell - 7; cell and cell2 have been declared as a string. Doing maths with strings will give you a type mismatch.

    Your other line throws a type mismatch when the cells value isn't a valid date. For example your code works fine when a cell value is 12/01/2001 but throws a type mismatch when the cells value is x.

     

    Thursday, June 22, 2006 3:06 PM
  • Hello again,

    With your date problem check the value of the cell before you use it using the IsDate() method. If IsDate(Range(cell)) = true then

    With your other problem I'm not sure if your trying to remove 7 from the value or if your trying to move 7 cells left or up. If your wanting to remove 7 from the value then use Range(cell2).Value = Range(cell).Value - 7, if your trying to move 7 cells back or up then have a look at the Range.Offset() function.

    Hope that helps

    Friday, June 23, 2006 9:44 AM
  • hi,

    Yeah your going to get errors all the time with that line. To take 7 days away from a date you use a function called DateAdd. You need to add -7 days.

    Like this... MsgBox DateAdd("d", -7, Now)

    You better also check Range(cell).Value is a valid date too.

    You also want to check the input from the input box, use CInt to check if its a number otherwise your for loop will give you errors.

    Also your two lines...

        cell = "L " & x      
        cell2 = "Y " & x   

    Should be inside the for loop.

    that should be you sorted

    Friday, June 23, 2006 3:06 PM
  • Scott, have a look at this and see if it helps, I haven't ran it but you should see that checks are done that makes sure your using the right data types for the functions your using.

    Sub cmdMGR_email_Dispatch_date_Click()

        Dim x, z As Integer
        Dim eeDisDate, mgrDisDate As String
           
        z = InputBox(Prompt:="Please enter the total number of employees to be processed this quarter.", _
            Title:="Maximum No of Employees", Default:="Type number here:")

    If IsNumeric(z) = True then   

        For x = 2 To z
       
            eeDisDateRef = "L" & x  'no space required in ref i.e "L2" not "L 2"
            mgrDisDateRef = "Y" & x   

    Dim eeDisDate as Date

    If IsDate(Range(eeDisDateRef).Value) = True then
    eeDisDate = CDate(Range(eeDisDateRef).Value)


            Range(mgrDisDateRef).Value = DateAdd("d", -7, eeDisDate)

    End If

        Next

        MsgBox "The Managers Email Dispatch date has now been updated."

    End If

    End Sub

    Wednesday, June 28, 2006 11:13 AM

All replies

  • Hi,

    cell2 = cell - 7; cell and cell2 have been declared as a string. Doing maths with strings will give you a type mismatch.

    Your other line throws a type mismatch when the cells value isn't a valid date. For example your code works fine when a cell value is 12/01/2001 but throws a type mismatch when the cells value is x.

     

    Thursday, June 22, 2006 3:06 PM
  • Hey

    Cheers got the first bit sorted but still stuck on the one with the dates - is there any way you can think of getting round that problem?

    Many thanks

    Sorry thought i had the first bit sorted but just tested it there and its bringing up a different runtime error 1004!

    i amended the code as shown below:

    Sub cmdMGR_email_Dispatch_date_Click()

        Dim x, z As Integer
        Dim cell, cell2 As String
           
        z = InputBox(Prompt:="Please enter the total number of employees to be processed this quarter.", _
            Title:="Maximum No of Employees", Default:="Type number here:")
       
        cell = "L " & x      'cell refers to Employee Dispatch Date
        cell2 = "Y " & x    'cell2 refers to Manager Dispatch date which is 1 week earlier than EE date
       
        For x = 2 To z
           
            Range(cell2) = Range(cell) - 7

        Next

        MsgBox "The Managers Email Dispatch date has now been updated."

    End Sub

    Any ideas?

    Friday, June 23, 2006 7:34 AM
  • Hello again,

    With your date problem check the value of the cell before you use it using the IsDate() method. If IsDate(Range(cell)) = true then

    With your other problem I'm not sure if your trying to remove 7 from the value or if your trying to move 7 cells left or up. If your wanting to remove 7 from the value then use Range(cell2).Value = Range(cell).Value - 7, if your trying to move 7 cells back or up then have a look at the Range.Offset() function.

    Hope that helps

    Friday, June 23, 2006 9:44 AM
  • Listen thank you so much for taking the time to help me - its saved me so many hours of debugging!!

    The date problem is now resolved and is working great! However, the second problem is still giving an error '1004'  

    I tried changing the code as u can see from below but unfortunately no joy as yet!

    BTW im trying to remove 7 from the value which is a date i.e. managers dispatch date is 1 week before the employees dispatch date.

    '*******************************************
    'Generate the managers email dispatch date
    '*******************************************

    Sub cmdMGR_email_Dispatch_date_Click()

        Dim x, z As Integer
        Dim cell, cell2 As String
           
        z = InputBox(Prompt:="Please enter the total number of employees to be processed this quarter.", _
            Title:="Maximum No of Employees", Default:="Type number here:")
       
        cell = "L " & x      'cell refers to Employee Dispatch Date
        cell2 = "Y " & x    'cell2 refers to Manager Dispatch date which is 1 week earlier than EE date
       
        For x = 2 To z
           
          Range(cell2).Value = Range(cell).Value - 7

        Next

        MsgBox "The Managers Email Dispatch date has now been updated."

    End Sub


    Again, really appreciate the help!

    Scott

    Friday, June 23, 2006 12:52 PM
  •  

    Friday, June 23, 2006 3:04 PM
  • hi,

    Yeah your going to get errors all the time with that line. To take 7 days away from a date you use a function called DateAdd. You need to add -7 days.

    Like this... MsgBox DateAdd("d", -7, Now)

    You better also check Range(cell).Value is a valid date too.

    You also want to check the input from the input box, use CInt to check if its a number otherwise your for loop will give you errors.

    Also your two lines...

        cell = "L " & x      
        cell2 = "Y " & x   

    Should be inside the for loop.

    that should be you sorted

    Friday, June 23, 2006 3:06 PM
  • Scott
    A bit more constructive criticism: 

    1.  The input box is shown twice with the same question.  What happens if someone enters different numbers?  It would be better to show it once and store input in a module-level variable.  Also if 4 is entered only 3 employees are processed!

    2. Give your variables better names like joinedDate instead of cell etc.

    3. The program should be capable of catering for all possibilities and not need changing because the year/quarter has changed.  The long If..ElseIf statement could be replaced by a simple function to calculate the award.

    Function Award(joinedDate As Date) As Currency
        If (Year(Date) - Year(joinedDate)) Mod 5 = 0 Then       'is (year - joined year) a multiple of 5?
            Award = Year(Date) - Year(joinedDate)
        Else
            Award = 0
        End If
    End Function

    Call it like this:  Range(cell2) = Award(CDate(Range(cell)))    '(might not need CDate if cell contains a valid date)
    The function can be easily expanded to cope with quarters by using Month(JoinedDate).

    hth
    Friday, June 23, 2006 8:58 PM
  • Hey thanks for the reply again!

    Know you've provided me with the answer but still not sure how to implement the revised line of code into my solution?

    Sub cmdMGR_email_Dispatch_date_Click()

        Dim x, z As Integer
        Dim eeDisDate, mgrDisDate As String
           
        z = InputBox(Prompt:="Please enter the total number of employees to be processed this quarter.", _
            Title:="Maximum No of Employees", Default:="Type number here:")
       
        For x = 2 To z
       
            eeDisDate = "L " & x 
            mgrDisDate = "Y " & x   e
           
            Range(mgrDisDate).Value = DateAdd("d", -7, eeDisDate)

        Next

        MsgBox "The Managers Email Dispatch date has now been updated."

    End Sub

    Changed the variable names (thanks for advice Navajo!) and had a go at what i thought the code might be but as you can see im still very much a beginner!

    Thanks again for the help folks

    Monday, June 26, 2006 12:48 PM
  • Scott, have a look at this and see if it helps, I haven't ran it but you should see that checks are done that makes sure your using the right data types for the functions your using.

    Sub cmdMGR_email_Dispatch_date_Click()

        Dim x, z As Integer
        Dim eeDisDate, mgrDisDate As String
           
        z = InputBox(Prompt:="Please enter the total number of employees to be processed this quarter.", _
            Title:="Maximum No of Employees", Default:="Type number here:")

    If IsNumeric(z) = True then   

        For x = 2 To z
       
            eeDisDateRef = "L" & x  'no space required in ref i.e "L2" not "L 2"
            mgrDisDateRef = "Y" & x   

    Dim eeDisDate as Date

    If IsDate(Range(eeDisDateRef).Value) = True then
    eeDisDate = CDate(Range(eeDisDateRef).Value)


            Range(mgrDisDateRef).Value = DateAdd("d", -7, eeDisDate)

    End If

        Next

        MsgBox "The Managers Email Dispatch date has now been updated."

    End If

    End Sub

    Wednesday, June 28, 2006 11:13 AM
  • Many thanks for all your help! Its finally came together and is working well.  Ill def be recommending these forums to others who are trying to learn VBA! :D
    Thursday, June 29, 2006 9:27 AM