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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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

D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
81
Peter_SSs
Peter_SSs
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
315
dshtesy
D
H
  • Solved
Delete the last character of cells in column "K" if it is equal to "X" or "x"
Replies
8
Views
126
Fuji
F
A
  • Question Question
Multiply an array based on date value entered.
Replies
8
Views
113
Fluff
Fluff
A
  • Locked
  • Question Question
Multiply an array based on date value entered.
Replies
1
Views
38
Fluff
Fluff

Forum statistics

Threads
1,223,876
Messages
6,175,123
Members
452,614
Latest member
MRSWIN2709

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