Arrays in VBA - error 13 type mismatch

ValVerPat · Aug 1, 2024

I want to calculate the time spent in column E by subtracting the Start stamp (column A) from the End stamp (column B).
What works fine with the formula in column H doesn't work with VBA.
When I uncomment the "Answers(i, 3) = DateEn(i, 1) - DateSt(i, 1)" line then the update of the month and year in columns C and D works fine.
So why the error for the update of time spent (column E)?

VBA Code:

Dim WS As Worksheet

Dim DateSt() As Long
Dim DateEn() As Long
Dim Answers() As Long

Dim D1 As Long
Dim i As Long
Dim l As Long

Set WS = Worksheets("Data")

Range("A1").Select
Selection.End(xlDown).Select
l = Selection.Row

DateSt = WS.Range("A2:A" & l)
DateEn = WS.Range("B2:B" & l)

D1 = UBound(DateSt, 1)

ReDim Answers(1 To D1, 1 To 3)

For i = 1 To D1
    Answers(i, 1) = Month(DateSt(i, 1))
    Answers(i, 2) = Year(DateSt(i, 1))
    Answers(i, 3) = DateEn(i, 1) - DateSt(i, 1)     ' This line generates the error. When commenting this line out then the update of the month and year in columns C and D works fine.
Next i

WS.Range(Range("C2"), Range("C2").Offset(D1 - 1, 2)).Value = Answers

Arrays-VBA-Error.xlsm

A

B

C

D

E

F

G

H

I

J

K

1

Start

End

Month

Year

Time Spent

Formula

Time Spent = End - Start

2

9/01/2024 10:51:10

9/01/2024 11:24:18

0,02300926

3

9/01/2024 11:25:19

9/01/2024 11:32:45

0,00516204

4

10/01/2024 10:12:10

10/01/2024 10:16:55

0,00329861

5

10/01/2024 10:40:03

10/01/2024 10:41:42

0,00114583

6

10/01/2024 10:42:50

10/01/2024 10:52:15

0,00653935

7

10/01/2024 11:10:18

10/01/2024 11:14:06

0,00263889

8

10/01/2024 11:20:12

10/01/2024 11:23:57

0,00260417

9

10/01/2024 11:24:44

10/01/2024 11:28:26

0,00256944

10

10/01/2024 11:30:34

10/01/2024 11:36:04

0,00381944

11

10/01/2024 11:37:40

10/01/2024 11:41:32

0,00268519

12

10/01/2024 13:24:57

10/01/2024 13:28:05

0,00217593

13

10/01/2024 13:28:05

10/01/2024 13:29:26

0,0009375

14

10/01/2024 13:29:26

10/01/2024 13:30:45

0,00091435

15

11/01/2024 8:13:51

11/01/2024 8:14:38

0,00054398

16

11/01/2024 9:34:05

11/01/2024 9:40:55

0,00474537

17

11/01/2024 9:40:55

11/01/2024 9:42:44

0,00126157

18

11/01/2024 10:08:25

11/01/2024 10:13:07

0,00326389

19

11/01/2024 10:31:13

11/01/2024 10:34:17

0,00212963

Data

Cell Formulas
Range	Formula
H2:H19	H2	=B2-A2

ValVerPat · Aug 3, 2024

kvsrinivasamurthy said:
If still problem is not solved please upload file without sensitive data.

Hi,
See attached my file.
I do not have an issue with the formulas, these are working fine. I've a problem with the code more specifically with this line:
Answers(i, 3) = DateEn(i, 1) - DateSt(i, 1)
When I uncomment this line then the update of the month and year in columns C and D by using the code works fine.
So why the error for the update by using the code of time spent (column E)?

Alex Blakenburg · Aug 3, 2024

Did you try Jeff's code in Post #2 with the modification of Dim Answers() As Variant ?

ValVerPat · Aug 3, 2024

Alex Blakenburg said:
Try @Jeffrey Mahoney's code in post #2 again and make this one change:

Rich (BB code):

Dim Answers() As Long Dim Answers() As Variant

PS: if the results show 0 check the column format and column width

Alex, yes I did. I keep getting error 13 for that code line: Answers(i, 3) = DateEn(i, 1) - DateSt(i, 1).
I also tried differrent column formats, all in vain.
What I don't understand is why the formula (as a test) in column H is working and not the update via the code.

Alex Blakenburg · Aug 4, 2024

You seem to have different online hours to those of us trying solve the issue, so the fastest way might be to put a copy of your test data on a share platform such as Dropbox, Google drive etc and make it available to anyone with the link and post the link here as @kvsrinivasamurthy has suggested.
Just make sure that the workbook has the code in it that you tried and didn't work and that the workbook still has the issue so we can see it happen.

If you want to give it one more try, this is the code that works for me on your XL2BB data, its @Jeffrey Mahoney's code with the change to Dim Answers() As Variant.
Note: I also changed the Last row to look from the bottom up instead of the top down and the output line sizing neither of which should be related to your issue.

Rich (BB code):

Sub JeffsCodeRePosted()

  Dim WS As Worksheet
  Dim DateSt() As Variant
  Dim DateEn() As Variant
  Dim Answers() As Variant
  
  
  Dim D1 As Long
  Dim i As Long
  Dim l As Long
  
  Set WS = Worksheets("Data")
  
  l = Range("A" & Rows.Count).End(xlUp).Row
  
  DateSt = WS.Range("A2:A" & l)
  DateEn = WS.Range("B2:B" & l)
  
  D1 = UBound(DateSt, 1)
  
  ReDim Answers(1 To D1, 1 To 3)
  
  For i = 1 To D1
      Answers(i, 1) = Month(DateSt(i, 1))
      Answers(i, 2) = Year(DateSt(i, 1))
      Answers(i, 3) = DateEn(i, 1) - DateSt(i, 1)     ' This line generates the error. When commenting this line out then the update of the month and year in columns C and D works fine.
  Next i
  
  WS.Range("C2").Resize(D1, UBound(Answers, 2)).Value = Answers

End Sub

ValVerPat · Aug 4, 2024

Alex Blakenburg said:
You seem to have different online hours to those of us trying solve the issue, so the fastest way might be to put a copy of your test data on a share platform such as Dropbox, Google drive etc and make it available to anyone with the link and post the link here as @kvsrinivasamurthy has suggested.
Just make sure that the workbook has the code in it that you tried and didn't work and that the workbook still has the issue so we can see it happen.

If you want to give it one more try, this is the code that works for me on your XL2BB data, its @Jeffrey Mahoney's code with the change to Dim Answers() As Variant.
Note: I also changed the Last row to look from the bottom up instead of the top down and the output line sizing neither of which should be related to your issue.

Rich (BB code):

Sub JeffsCodeRePosted() Dim WS As Worksheet Dim DateSt() As Variant Dim DateEn() As Variant Dim Answers() As Variant Dim D1 As Long Dim i As Long Dim l As Long Set WS = Worksheets("Data") l = Range("A" & Rows.Count).End(xlUp).Row DateSt = WS.Range("A2:A" & l) DateEn = WS.Range("B2:B" & l) D1 = UBound(DateSt, 1) ReDim Answers(1 To D1, 1 To 3) For i = 1 To D1 Answers(i, 1) = Month(DateSt(i, 1)) Answers(i, 2) = Year(DateSt(i, 1)) Answers(i, 3) = DateEn(i, 1) - DateSt(i, 1) ' This line generates the error. When commenting this line out then the update of the month and year in columns C and D works fine. Next i WS.Range("C2").Resize(D1, UBound(Answers, 2)).Value = Answers End Sub

Hi Alex,
Yes indeed, see link below. FYI, the "Update"-bottom is my initial code. The "Alex"-button is your code.

Arrays-VBA-Error.xlsm

drive.google.com

I copied your code en tried it, however still the same problem.

Alex Blakenburg · Aug 4, 2024

What is your default date format ?
eg mm/dd/yyyy or dd/mm/yyyy

Alex Blakenburg · Aug 4, 2024

Your dates are all text values. Give this a try:
Note: It also converts Column A & B to true dates.

VBA Code:

Sub ConvertTextDateAddDateCalcs()

  Dim WS As Worksheet
  Dim DateSt() As Variant
  Dim DateEn() As Variant
  Dim Answers() As Variant
  Dim dtDateSt As Date
  Dim dtDateEn As Date
   
  Dim D1 As Long
  Dim i As Long
  Dim l As Long
  
  Set WS = Worksheets("Data")
  
  l = Range("A" & Rows.Count).End(xlUp).Row
  
  DateSt = WS.Range("A2:A" & l).Value2
  DateEn = WS.Range("B2:B" & l).Value2
  
  D1 = UBound(DateSt, 1)
  
  ReDim Answers(1 To D1, 1 To 5)
  
  For i = 1 To D1
      dtDateSt = CDate(DateSt(i, 1))
      dtDateEn = CDate(DateEn(i, 1))
      Answers(i, 1) = dtDateSt
      Answers(i, 2) = dtDateEn
      Answers(i, 3) = Month(dtDateSt)
      Answers(i, 4) = Year(dtDateSt)
      Answers(i, 5) = dtDateEn - dtDateSt  
  Next i
  
  WS.Range("A2").Resize(D1, UBound(Answers, 2)).Value = Answers

End Sub

ValVerPat · Aug 4, 2024

Alex Blakenburg said:

Your dates are all text values. Give this a try:
Note: It also converts Column A & B to true dates.

VBA Code:

Sub ConvertTextDateAddDateCalcs()

  Dim WS As Worksheet
  Dim DateSt() As Variant
  Dim DateEn() As Variant
  Dim Answers() As Variant
  Dim dtDateSt As Date
  Dim dtDateEn As Date
  
  Dim D1 As Long
  Dim i As Long
  Dim l As Long
 
  Set WS = Worksheets("Data")
 
  l = Range("A" & Rows.Count).End(xlUp).Row
 
  DateSt = WS.Range("A2:A" & l).Value2
  DateEn = WS.Range("B2:B" & l).Value2
 
  D1 = UBound(DateSt, 1)
 
  ReDim Answers(1 To D1, 1 To 5)
 
  For i = 1 To D1
      dtDateSt = CDate(DateSt(i, 1))
      dtDateEn = CDate(DateEn(i, 1))
      Answers(i, 1) = dtDateSt
      Answers(i, 2) = dtDateEn
      Answers(i, 3) = Month(dtDateSt)
      Answers(i, 4) = Year(dtDateSt)
      Answers(i, 5) = dtDateEn - dtDateSt 
  Next i
 
  WS.Range("A2").Resize(D1, UBound(Answers, 2)).Value = Answers

End Sub

Hi Alex,
Works like a charm !
Thanks a lot for looking into this.

Alex Blakenburg · Aug 4, 2024

You're welcome. Glad we could help.

Arrays in VBA - error 13 type mismatch

ValVerPat

New Member

Attachments

ValVerPat

New Member

Excel Facts

Alex Blakenburg

MrExcel MVP

ValVerPat

New Member

Alex Blakenburg

MrExcel MVP

ValVerPat

New Member

Arrays-VBA-Error.xlsm

Alex Blakenburg

MrExcel MVP

Alex Blakenburg

MrExcel MVP

ValVerPat

New Member

Alex Blakenburg

MrExcel MVP

Similar threads

Forum statistics

Share this page

Arrays in VBA - error 13 type mismatch

New Member

Attachments

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock