Arrays in VBA - error 13 type mismatch

V

ValVerPat

New Member
Joined
Aug 1, 2024
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
1StartEndMonthYearTime SpentFormulaTime Spent = End - Start
29/01/2024 10:51:109/01/2024 11:24:180,02300926
39/01/2024 11:25:199/01/2024 11:32:450,00516204
410/01/2024 10:12:1010/01/2024 10:16:550,00329861
510/01/2024 10:40:0310/01/2024 10:41:420,00114583
610/01/2024 10:42:5010/01/2024 10:52:150,00653935
710/01/2024 11:10:1810/01/2024 11:14:060,00263889
810/01/2024 11:20:1210/01/2024 11:23:570,00260417
910/01/2024 11:24:4410/01/2024 11:28:260,00256944
1010/01/2024 11:30:3410/01/2024 11:36:040,00381944
1110/01/2024 11:37:4010/01/2024 11:41:320,00268519
1210/01/2024 13:24:5710/01/2024 13:28:050,00217593
1310/01/2024 13:28:0510/01/2024 13:29:260,0009375
1410/01/2024 13:29:2610/01/2024 13:30:450,00091435
1511/01/2024 8:13:5111/01/2024 8:14:380,00054398
1611/01/2024 9:34:0511/01/2024 9:40:550,00474537
1711/01/2024 9:40:5511/01/2024 9:42:440,00126157
1811/01/2024 10:08:2511/01/2024 10:13:070,00326389
1911/01/2024 10:31:1311/01/2024 10:34:170,00212963
Data
Cell Formulas
RangeFormula
H2:H19H2=B2-A2
 

Attachments

  • Data.jpg
    Data.jpg
    141.3 KB · Views: 4
Sort by date Sort by votes
kvsrinivasamurthy said:
If still problem is not solved please upload file without sensitive data.
Click to expand...
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)?
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Did you try Jeff's code in Post #2 with the modification of Dim Answers() As Variant ?
 
Upvote 0
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
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
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
Click to expand...

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 drive.google.com

I copied your code en tried it, however still the same problem.
 
Upvote 0
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
 
Upvote 0
Solution
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
Click to expand...
Hi Alex,
Works like a charm !
Thanks a lot for looking into this.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
69
sanrama
S
Dazzawm
  • Solved
Macro To Find Latest Date To Corresponding Data In Column B And Add To Sheet 2
Replies
7
Views
289
Fuji
F
sharky12345
  • Solved
Issue trying to identify earliest date & time
2
Replies
15
Views
450
sharky12345
sharky12345
D
  • Solved
highs and lows
2
Replies
15
Views
254
felixstraube
F
A
  • Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
385
myall_blues
myall_blues

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top