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

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
This is the way I would change it. The DateST and DateEn were Dim as long which really doesn't work to bring in ranges. Variant works better

VBA Code: 
Sub Idunno()

  Dim WS As Worksheet
  Dim DateSt() As Variant
  Dim DateEn() As Variant
  Dim Answers() As Long
  
  Dim D1 As Long
  Dim i As Long
  Dim L As Long
  
  Set WS = Worksheets("Data")
  
  L = Range("A1").End(xlDown).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

End Sub
 
Upvote 0
Jeffrey Mahoney said:
This is the way I would change it. The DateST and DateEn were Dim as long which really doesn't work to bring in ranges. Variant works better

VBA Code: 
Sub Idunno()

  Dim WS As Worksheet
  Dim DateSt() As Variant
  Dim DateEn() As Variant
  Dim Answers() As Long
 
  Dim D1 As Long
  Dim i As Long
  Dim L As Long
 
  Set WS = Worksheets("Data")
 
  L = Range("A1").End(xlDown).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

End Sub
Click to expand...
Thanks for responding, Jeff
Just copied your code in my worksheet and still have the same error. Any other thoughts.
 
Upvote 0
This converts column A & B to serial date & time
So that the formula in the sheet should work.
Code: 
Sub test()
    Dim a, i&, ii&, x, y, myDate As Long, myTime As Single
    With [a1].CurrentRegion.Resize(, 2)
        a = .Value
        For i = 2 To UBound(a, 1)
            For ii = 1 To 2
                x = Split(a(i, ii))
                y = Split(x(0), "/")
                myDate = DateSerial(y(2), y(0), y(1))
                myTime = TimeValue(x(1))
                a(i, ii) = myDate + myTime
            Next
        Next
        .Value = a
        .NumberFormatLocal = "m/dd/yyyy hh:mm:ss"
    End With
End Sub
 
Upvote 0
Change this
VBA Code: 
Dim DateSt() As Long
Dim DateEn() As Long
Dim Answers() As Long
as
VBA Code: 
Dim DateSt() As double
Dim DateEn() As double
Dim Answers() As double

In
VBA Code: 
 Answers(i, 3) = DateEn(i, 1) - DateSt(i, 1)
Answers(i, 3) is a decimal value can not be stored in variable declared as long. It can store only integer values.
 
Upvote 0
Fuji said:
Sub test() Dim a, i&, ii&, x, y, myDate As Long, myTime As Single With [a1].CurrentRegion.Resize(, 2) a = .Value For i = 2 To UBound(a, 1) For ii = 1 To 2 x = Split(a(i, ii)) y = Split(x(0), "/") myDate = DateSerial(y(2), y(0), y(1)) myTime = TimeValue(x(1)) a(i, ii) = myDate + myTime Next Next .Value = a .NumberFormatLocal = "m/dd/yyyy hh:mm:ss" End With End Sub
Click to expand...
Thanks for responding, Fuji
Just copied your code in my worksheet and tried it. As you can see below the conversion doesn't work 100 %.
9/01/2024 10:51:10 -> 9/01/yyyy hh:51:10

By the way the formula in the sheet is working fine. The issue occurs when I run the VBA code.
 
Upvote 0
kvsrinivasamurthy said:
Change this
VBA Code: 
Dim DateSt() As Long
Dim DateEn() As Long
Dim Answers() As Long
as
VBA Code: 
Dim DateSt() As double
Dim DateEn() As double
Dim Answers() As double

In
VBA Code: 
 Answers(i, 3) = DateEn(i, 1) - DateSt(i, 1)
Answers(i, 3) is a decimal value can not be stored in variable declared as long. It can store only integer values.
Click to expand...
Thanks for responding, Kvsrinivasamurthy
Just applied your code in my worksheet and still have the same error. Any other thoughts.
 
Upvote 0
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
 
Last edited:
Upvote 0
Strange...
ValVerPat said:
9/01/2024 10:51:10 -> 9/01/yyyy hh:51:10
Click to expand...
Before;
Book1
ABCDEFGHI
1StartEndMonthYearTime SpentFormulaTime Spent = End - Start
29/01/2024 10:51:109/01/2024 11:24:18#VALUE!#VALUE!#VALUE!#VALUE!
39/01/2024 11:25:199/01/2024 11:32:45#VALUE!#VALUE!#VALUE!#VALUE!
410/01/2024 10:12:1010/01/2024 10:16:55#VALUE!#VALUE!#VALUE!#VALUE!
510/01/2024 10:40:0310/01/2024 10:41:42#VALUE!#VALUE!#VALUE!#VALUE!
610/01/2024 10:42:5010/01/2024 10:52:15#VALUE!#VALUE!#VALUE!#VALUE!
710/01/2024 11:10:1810/01/2024 11:14:06#VALUE!#VALUE!#VALUE!#VALUE!
810/01/2024 11:20:1210/01/2024 11:23:57#VALUE!#VALUE!#VALUE!#VALUE!
910/01/2024 11:24:4410/01/2024 11:28:26#VALUE!#VALUE!#VALUE!#VALUE!
1010/01/2024 11:30:3410/01/2024 11:36:04#VALUE!#VALUE!#VALUE!#VALUE!
1110/01/2024 11:37:4010/01/2024 11:41:32#VALUE!#VALUE!#VALUE!#VALUE!
1210/01/2024 13:24:5710/01/2024 13:28:05#VALUE!#VALUE!#VALUE!#VALUE!
1310/01/2024 13:28:0510/01/2024 13:29:26#VALUE!#VALUE!#VALUE!#VALUE!
1410/01/2024 13:29:2610/01/2024 13:30:45#VALUE!#VALUE!#VALUE!#VALUE!
1511/01/2024 8:13:5111/01/2024 8:14:38#VALUE!#VALUE!#VALUE!#VALUE!
1611/01/2024 9:34:0511/01/2024 9:40:55#VALUE!#VALUE!#VALUE!#VALUE!
1711/01/2024 9:40:5511/01/2024 9:42:44#VALUE!#VALUE!#VALUE!#VALUE!
1811/01/2024 10:08:2511/01/2024 10:13:07#VALUE!#VALUE!#VALUE!#VALUE!
1911/01/2024 10:31:1311/01/2024 10:34:17#VALUE!#VALUE!#VALUE!#VALUE!
Sheet1
Cell Formulas
RangeFormula
C2:C19C2=MONTH(A2)
D2:D19D2=YEAR(A2)
E2:E19E2=B2-A2
H2:H19H2=B2-A2


After; I got
Book1
ABCDEFGHI
1StartEndMonthYearTime SpentFormulaTime Spent = End - Start
29/01/2024 10:51:109/01/2024 11:24:18920240.02300930.023009
39/01/2024 11:25:199/01/2024 11:32:45920240.0051620.005162
410/01/2024 10:12:1010/01/2024 10:16:551020240.00329860.003299
510/01/2024 10:40:0310/01/2024 10:41:421020240.00114580.001146
610/01/2024 10:42:5010/01/2024 10:52:151020240.00653940.006539
710/01/2024 11:10:1810/01/2024 11:14:061020240.00263890.002639
810/01/2024 11:20:1210/01/2024 11:23:571020240.00260420.002604
910/01/2024 11:24:4410/01/2024 11:28:261020240.00256940.002569
1010/01/2024 11:30:3410/01/2024 11:36:041020240.00381940.003819
1110/01/2024 11:37:4010/01/2024 11:41:321020240.00268520.002685
1210/01/2024 13:24:5710/01/2024 13:28:051020240.00217590.002176
1310/01/2024 13:28:0510/01/2024 13:29:261020240.00093750.000938
1410/01/2024 13:29:2610/01/2024 13:30:451020240.00091430.000914
1511/01/2024 08:13:5111/01/2024 08:14:381120240.0005440.000544
1611/01/2024 09:34:0511/01/2024 09:40:551120240.00474540.004745
1711/01/2024 09:40:5511/01/2024 09:42:441120240.00126160.001262
1811/01/2024 10:08:2511/01/2024 10:13:071120240.00326390.003264
1911/01/2024 10:31:1311/01/2024 10:34:171120240.00212960.00213
Sheet1
Cell Formulas
RangeFormula
C2:C19C2=MONTH(A2)
D2:D19D2=YEAR(A2)
E2:E19E2=B2-A2
H2:H19H2=B2-A2
 
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,173
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