Adding row to table, populated with Array data stores numbers and dates as text

ok, in the code below I take input from a userform, pass it to an array, and add a new line to a table, with the data in the array.
All works great, except, dates and numbers are being put into the table as text. I don't want to have to go in afterwards and manually update the cells. These need to be stored as Numbers / dates as other tables and reports feed from them.

When I go look at the table, changing the format of these cells does nothing, I also then have to click into the cell and hit enter to update it, before it formats correctly. At the moment these only a few lines in them, next week there will be hundreds getting put in. It won't be feasible to go manually through them all afer loading (And it defeats the purpose of the userform in the first place)

Any help would be greatly appreciated.

Dim sTitle As String, sProject As String, sProspect As String, sName As String, MyArray2() As String, sTemp() As String
Dim lHours As Long
Dim sDate As Date, eDate As Date
Dim i As Integer
Dim Tbl As ListObject
Dim NewRow As ListRow
Dim lTemp As Long


sProject = TextBox1.Value
sProspect = ComboBox6.Value
sTitle = TextBox2.Value

' Build array from textbox5 delimited with vbcrlf
' add each line to array
' With each item split(MyArray(i),"-") to get items 1 Name, 2 Hours, 3 start date, 4 end date
Set Tbl = Sheets("Data_Tables").Range("Projects").ListObject
MyArray() = Split(TextBox5.Value, vbCrLf)

For i = 0 To UBound(MyArray())
    sTemp() = Split(MyArray(i), "-")
    sName = sTemp(0)
    lHours = sTemp(1)
    sDate = sTemp(2)
    sMonth = Format(Month(sDate), "mmm")
    sYear = Format(Year(sDate), "YY")
    eDate = sTemp(3)
    sMonth1 = Format(Month(eDate), "mmm")
    sYear1 = Format(Year(eDate), yy)
    Dim MyArray3() As String

    ReDim Preserve MyArray2(6)
            MyArray2(0) = sProject
            MyArray2(1) = sProspect
            MyArray2(2) = sTitle
            MyArray2(3) = Format(sDate, "dd-mmm-yy")
            MyArray2(4) = Format(eDate, "dd-mmm-yy")
            MyArray2(5) = sName
            MyArray2(6) = lHours
    Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)
    NewRow.Range = MyArray2
    'iRow = NewRow.Row
    irow = Tbl.ListRows.Count + 2
    sTemp2 = Range("AB" & irow - 1).Formula
    Range("AB" & irow).Formula = sTemp2
    sTemp2 = Range("AC" & irow - 1).Formula
    Range("AC" & irow).Formula = sTemp2

Next i
End Sub

Open in new window

LVL 2
Conor_NewmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
The Format function returns a string, so that's why you may be having problems. You need to produce an actual date/time serial number instead.

You can do this either using the CDate function (converting a single string directly into a date/time serial number) or else the DateSerial function (which takes the year, month and day as separate parameters).

Note that VBA expects dates to be in MDY format. When the date is ambiguous (such as 11-02-2015) you may therefore have better success using the DateSerial function than using CDate. CDate will return the serial number for November 2, 2015, whereas DateSerial("2015","02","11") will return the expected February 11, 2015.
Conor_NewmanAuthor Commented:
Thank you! Will try that now, the other one is lHours variable, it has a number in it, its declared as long, but it also is entered in the table as text.
byundtMechanical EngineerCommented:
How is the table formatted? The hour column should be formatted as a Number with zero decimal places if you want the hour stored as a whole number.

If you want the hour column to receive a time, then use the TimeSerial function:
MyArray2(6) = TimeSerial(lHour, 0, 0)

I should also mention that DateSerial and TimeSerial can accept either numbers or text as their input parameters.

It the above tweaks aren't working for you, could you please post a sample workbook that reproduces your problem?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Conor_NewmanAuthor Commented:
Didn't work. It's the right format, its just for some unknown reason being stored as text in the table. Even when I clock manually on the cell and check the format, its right. But the data is still stored as text.

I've attached the workbook, with all macros / userform in it. The Issue is on the first page of the userform, Fill in project detail, with one or more resources, (Must be added BEFORE clicking to add project) and then add the project. You'll see the bug in the data in the Projects table then.
staff-loading-sanitised.xlsm
byundtMechanical EngineerCommented:
I changed your String arrays MyArray and MyArray2 to Variants. This was your big problem. I also dropped the Preserve keyword when you assign values to MyArray2 and dropped the parentheses when you assign values to MyArray.
Private Sub CommandButton3_Click()
' Add project to table: Projects Sheet: Data_Tables
' Use insert line to table
Dim sTitle As String, sProject As String, sProspect As String
Dim sName As String, MyArray2 As Variant, sTemp() As String, MyArray As Variant
Dim lHours As Long
Dim sDate As Date, eDate As Date
Dim i As Integer
Dim Tbl As ListObject
Dim NewRow As ListRow
Dim lTemp As Long


sProject = TextBox1.Value
sProspect = ComboBox6.Value
sTitle = TextBox2.Value

' Build array from textbox5 delimited with vbcrlf
' add each line to array



' With each item split(MyArray(i),"-") to get items 1 Name, 2 Hours, 3 start date, 4 end date
Set Tbl = Sheets("Data_Tables").Range("Projects").ListObject
MyArray = Split(TextBox5.Value, vbCrLf)

For i = 0 To UBound(MyArray)
    sTemp() = Split(MyArray(i), "-")
    sName = sTemp(0)
    lHours = sTemp(1)
    sDate = sTemp(2)
    sMonth = Format(Month(sDate))
    sYear = Format(Year(sDate))
    sDay = Format(Day(sDate))
    sDate = DateSerial(sYear, sMonth, sDay)
    eDate = sTemp(3)
    sMonth1 = Format(Month(eDate))
    sYear1 = Format(Year(eDate))
    sDay1 = Format(Day(eDate))
    eDate = DateSerial(sYear1, sMonth1, sDay1)
    
    Dim MyArray3() As String

    ReDim MyArray2(6)
            MyArray2(0) = sProject
            MyArray2(1) = sProspect
            MyArray2(2) = sTitle
            MyArray2(3) = sDate 'Format(sDate, "dd-mmm-yy")
            MyArray2(4) = eDate 'Format(eDate, "dd-mmm-yy")
            MyArray2(5) = sName
            MyArray2(6) = lHours
    Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)
    NewRow.Range.Value = MyArray2
    'iRow = NewRow.Row
    irow = Tbl.ListRows.Count + 2
    sTemp2 = Range("AB" & irow - 1).Formula
    Range("AB" & irow).Formula = sTemp2
    sTemp2 = Range("AC" & irow - 1).Formula
    Range("AC" & irow).Formula = sTemp2

Next i


End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Conor_NewmanAuthor Commented:
You Sir, are a gentleman and a scholar! Thank you! That has been frying my brain for hours!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.