VBA Code runs in excel 2013 but not in 2010

Question

The following code I've written using excel 2013 works on excel 2013, but when trying it on my company's 2010 version of excel it produces an error when I click Cancel button on inputbox dialog box and stops at the line of code:

EntryDate = CDate(InputBox("Insert Date", "Insert Date", vbOKCancel))

says: type Mismatch and the number is: Run-time error'13':

Why?

    Sub InsertNewEntry()
'
' InsertNewEntry Macro
' To Insert New Entry for exchange rates
'

Dim LastRow As Integer, EntryDate As Date
Dim EURtoUSD As Double, JODtoUSD As Double, ILStoUSD As Double

' determine the number of the last row entered
LastRow = ThisWorkbook.Worksheets("Exchange Rates Template").Cells(Rows.Count, 2).End(xlUp).Row

'determine if last date is last day of the year
   If Cells(LastRow, 2) = #12/31/2014# Then
    MsgBox "You are not allowed to insert a date above " & "31/12/ " & Cells(4, 1).Value
    Exit Sub
   Else
    Cells(LastRow, 4).Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    Cells(LastRow, 8).Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    Cells(LastRow, 12).Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    Cells(LastRow + 1, 2).Select
    EntryDate = CDate(InputBox("Insert Date", "Insert Date", vbOKCancel))
      If EntryDate <> "" Then
       Cells(LastRow + 1, 2) = EntryDate
       Cells(LastRow + 1, 3) = "EUR to USD"
       Cells(LastRow + 1, 6) = EntryDate
       Cells(LastRow + 1, 7) = "JOD to USD"
       Cells(LastRow + 1, 10) = EntryDate
       Cells(LastRow + 1, 11) = "ILS to USD"
      Else
       Cells(LastRow + 1, 2).Select
       Selection.ListObject.ListRows(LastRow - 3).Delete
       Cells(LastRow + 1, 6).Select
       Selection.ListObject.ListRows(LastRow - 3).Delete
       Cells(LastRow + 1, 10).Select
       Selection.ListObject.ListRows(LastRow - 3).Delete
       Exit Sub

      End If
   End If

End Sub

Siddharth Rout · Accepted Answer · 2014-01-14T13:51:45.480

6

Change EntryDate As Date to EntryDate As Variant

and also change the line,

EntryDate = CDate(InputBox("Insert Date", "Insert Date", vbOKCancel))` to

to

EntryDate = InputBox("Insert Date", "Insert Date", vbOKCancel)

and after that line

Add this line

If EntryDate = False Then Exit Sub

After this line, you may write

EntryDate  = Cdate(EntryDate)

And the reason is simple because Cdate(False) will give you 00:00:00 and Cdate(True) will give you 29/12/1899

Followup from comments

Is this what you are trying?

Dim EntryDate As Variant

EntryDate = InputBox("Insert Date", "Insert Date", vbOKCancel)

If EntryDate = False Then Exit Sub

If EntryDate <> "" Then
    EntryDate = CDate(EntryDate)
    Cells(LastRow + 1, 2).Value = EntryDate
    Cells(LastRow + 1, 3).Value = "EUR to USD"
    Cells(LastRow + 1, 6).Value = EntryDate
    Cells(LastRow + 1, 7).Value = "JOD to USD"
    Cells(LastRow + 1, 10).Value = EntryDate
    Cells(LastRow + 1, 11).Value = "ILS to USD"
Else
    Cells(LastRow + 1, 2).ListObject.ListRows(LastRow - 3).Delete
    Cells(LastRow + 1, 6).ListObject.ListRows(LastRow - 3).Delete
    Cells(LastRow + 1, 10).ListObject.ListRows(LastRow - 3).Delete
    Exit Sub
End If

edited Jan 14 '14 at 13:51

answered Jan 14 '14 at 13:19

Siddharth Rout

147,039
17
206
250

ok what should I do with the else statement? it will be alone without and if statement? – Jan 14 '14 at 13:24
One moment updating my post – Siddharth Rout Jan 14 '14 at 13:25
2

+1 quick answer! I haven't even had time to read the question:) – Dmitry Pavliv Jan 14 '14 at 13:26
@SiddharthRout Yes this is what I am trying to do, and Cdate here because I want the application to insert the date value as a date format so Filtering will be supported – Jan 14 '14 at 13:33
@MohammadTaha: Yeah I understood that so I removed that comment :) – Siddharth Rout Jan 14 '14 at 13:34
@SiddharthRout by the way,, nice shortening for the last three lines :) – Jan 14 '14 at 13:40
2

@MohammadTaha: [HERE](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) is my mantra :p – Siddharth Rout Jan 14 '14 at 13:41
@SiddharthRout the type mismatch error still triggers when I click cancel – Jan 14 '14 at 13:48
@SiddharthRout this one EntryDate = CDate(EntryDate) – Jan 14 '14 at 13:52
@MohammadTaha: See my previous comment ;) – Siddharth Rout Jan 14 '14 at 13:53
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/45202/discussion-between-siddharth-rout-and-mohammad-taha) – Siddharth Rout Jan 14 '14 at 13:56

VBA Code runs in excel 2013 but not in 2010

1 Answers1