Runtime error '13' - type mismatch problem! Again...  
Author Message
Scott Boyd





PostPosted: Visual Basic for Applications (VBA), Runtime error '13' - type mismatch problem! Again... Top

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 s****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 de****.

'***********************************************
' 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



Microsoft ISV Community Center Forums2  
 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), Runtime error '13' - type mismatch problem! Again... Top

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.



 
 
Scott Boyd





PostPosted: Visual Basic for Applications (VBA), Runtime error '13' - type mismatch problem! Again... Top

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


 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), Runtime error '13' - type mismatch problem! Again... Top

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



 
 
Scott Boyd





PostPosted: Visual Basic for Applications (VBA), Runtime error '13' - type mismatch problem! Again... Top

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


 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), Runtime error '13' - type mismatch problem! Again... Top

 



 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), Runtime error '13' - type mismatch problem! Again... Top

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



 
 
Navajo





PostPosted: Visual Basic for Applications (VBA), Runtime error '13' - type mismatch problem! Again... Top

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

 
 
Scott Boyd





PostPosted: Visual Basic for Applications (VBA), Runtime error '13' - type mismatch problem! Again... Top

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


 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), Runtime error '13' - type mismatch problem! Again... Top

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



 
 
Scott Boyd





PostPosted: Visual Basic for Applications (VBA), Runtime error '13' - type mismatch problem! Again... Top

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