Wrong formatting of VBA generation

[EnergyAmber]

Hello. I have a problem when I am trying to information from Excel cells into the array consisting from this kind of info. All this format is text.

The code related to arrays looks like that (not the whole code) I have defined the arrays as a String and I just transfer the same content.

Dim dt_array(), dt_date(1 To 289), dt_time(1 To 289) As String

dt_array = Workbooks("Smacro.xlsm").Worksheets("Sheet1").Range("A2:A290").Value
Workbooks("Smacro.xlsm").worksheets("Sheet2").Range("A2:A290") = dt_array()

For i = 1 To 289
        'dt_date(i) = dt_array(i, 1)
        dt_date(i) = Left(dt_array(i, 1), 10)
       dt_time(i) = Right(dt_array(i, 1), 8)
    Next i

 
Dim Destination1, Destination2 As Range
    Set Destination1 = Workbooks("Smacro.xlsm").Worksheets("Sheet2").Range("B2")
    Destination1.Resize(UBound(dt_date, 1)).Value = dt_date
    Set Destination2 = Workbooks("Smacro.xlsm").Worksheets("Sheet2").Range("C2")
    Destination2.Resize(UBound(dt_time, 1)).Value = dt_time

Even though I look the assigned parameter values, which are correct, it assigns the cells values like that while using formatting Range("A2:C290").NumberFormat = "@"

It gives those values. Why does it paste the time 00:00:00 in every cell, when it has to have a 5-minute difference? dt_time values inside the array are correct.

 

[RobP]

Hi,

I believe the issue is with your last line of code, but I don’t know how to correct it myself. I am also suspecting that your data in columnB is incorrect, but you just can’t see it as it’s all the same value. Try changing those dates to different ones to check it. I suspect then that line destination1.resize would also be an issue.

sorry not to have the answerright now.

Rob

 

[StephenCrump]

Compare these codes:

Sub Test1() 'what you're doing, in effect

    Dim dt_date(1 To 3) As String
    
    dt_date(1) = "A"
    dt_date(2) = "B"
    dt_date(3) = "C"
    
    Range("A1:A3").Value = dt_date

End Sub
Sub Test2() 'Alternative 1

    Dim dt_date(1 To 3, 1 To 1) As String
    
    dt_date(1, 1) = "A"
    dt_date(2, 1) = "B"
    dt_date(3, 1) = "C"
    
    Range("A1:A3").Value = dt_date

End Sub
Sub Test3() 'Alternative 2

    Dim dt_date(1 To 3) As String
    
    dt_date(1) = "A"
    dt_date(2) = "B"
    dt_date(3) = "C"
    
    Range("A1:A3").Value = Application.Transpose(dt_date)

End Sub

 

[Alex Blakenburg]

In addition to @StephenCrump's explanation on why your output didn't give you the desired results, I don't know if you realise that your line

Dim dt_array(), dt_date(1 To 289), dt_time(1 To 289) As String

means that only the last item (dt_time) is declared as a string and the other 2 are left as the default which is variant.
You need this to declare them all as a string

Dim dt_array() as String, dt_date(1 To 289) as String, dt_time(1 To 289) As String

Do you really want the output to be Text ? It might look ok but Dates and Time as text really limit your functionality.

In the below I have tried to stick fairly closely to what you had, it uses Stephen's alternative 2 method.
If you really want the output as text change the 2 references to "Date" on the first line to "String"
I have left the original array as variant.

Sub SplitDateTime()

    Dim dt_array(), dt_date() As Date, dt_time() As Date
    Dim i As Long    
    Dim wsSrc As Worksheet
    Dim wsDest As Worksheet
    
    Set wsSrc = Workbooks("Smacro.xlsm").Worksheets("Sheet1")
    Set wsDest = Workbooks("Smacro.xlsm").Worksheets("Sheet2")

    dt_array = wsSrc.Range("A2:A" & wsSrc.Cells(Rows.Count, "A").End(xlUp).Row).Value
    wsDest.Range("A2").Resize(UBound(dt_array)) = dt_array()
    
    ReDim dt_date(1 To UBound(dt_array), 1 To 1), dt_time(1 To UBound(dt_array), 1 To 1)

    For i = 1 To UBound(dt_array)
        dt_date(i, 1) = Left(dt_array(i, 1), 10)
        dt_time(i, 1) = Right(dt_array(i, 1), 8)
    Next i

    wsDest.Range("B2").Resize(UBound(dt_date)).Value = dt_date
    wsDest.Range("C2").Resize(UBound(dt_time)).Value = dt_time
    
    If VarType(dt_date) <> (vbArray + vbString) Then
        wsDest.Range("B2").Resize(UBound(dt_date)).NumberFormat = "yyyy-mm-dd"
    End If
    
    If VarType(dt_time) <> (vbArray + vbString) Then
        wsDest.Range("C2").Resize(UBound(dt_time)).NumberFormat = "hh:mm:ss"
    End If

End Sub

 

[bebo021999]

Assum column A is text or actual date-time. Use value2, instead of Value.

dt_array = Workbooks("Smacro.xlsm").Worksheets("Sheet1").Range("A2:A290").Value2