Excel 2010 Table, Add New row via VBA and Loop through all rows 1

[JasonEnsor]

Hi Guys,

I have started to run in to some issues with how I am adding data to an excel workbook, excel is changing some text in to a date and time format. I thought it might be easier to setup my data in a table and set the formatting for the table in the hope that the format carries on through the table.

I recorded the following Macro after creating a table to see how to do this.

Range("A5").Select
    ActiveCell.FormulaR1C1 = "name"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "something"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "06/05/2013"

Is this the best way to do it? just adding the data to the next free row? I am assuming that Excel is automatically causing the table to expand.

Any Help would be appreciated



Regards

J.

 

[SkipVought]

hi,

excel is changing some text in to a date and time

Yes, Excel will CHANGE some TEXT values to NUMERIC values, and you should be aware under wha circumstances this will occur. faq68-5827

Tables: I recommend using Structured Tables for an overwhelming number of reasons. There is just so much more that you can do easier and better with data in/from structured tables. This faq I wrote before Structure Tables became a feature, but it is sound information. faq68-5184

If you were adding to a table, let's say Table1, table top left cell A1:

    Dim ws As Worksheet, lo As ListObject
    Dim lFirstRow As Long, lNextRow As Long, iFirstCol As Integer, iLastCol As Integer
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set lo = ws.ListObjects(1)
    
    With lo
        lFirstRow = .Range.Row
        lNextRow = .Range.Rows.Count + lFirstRow
        iFirstCol = .Range.Column
        iLastCol = .Range.Columns.Count + iFirstCol - 1
        
        .Resize Range(ws.Cells(lFirstRow, iFirstCol), ws.Cells(lNextRow, iLastCol))
        
        ws.Cells(lNextRow, "a").Value = "name"
        ws.Cells(lNextRow, "b").Value = "something"
        With ws.Cells(lNextRow, "c")
            .NumberFormat = "@" '[b]this is TEXT format[/b]
            .Value = "06/05/2013"
        End With
        
    End With

Don't understand why you'd want a date as text, but there it is.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I'd much prefer to use an ACTUAL date instead...

        ws.Cells(lNextRow, "c").Value = DateSerial(2013, 6, 5)

And as well, literals would typically not be used to assign values to a table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JasonEnsor]

Hi Skip,

Thanks for the info. If the spreadsheet was purely for my use I wouldn't have a date as text, however as it is for a user who insists on dates following the UK date convention of dd mm yyyy and invoices go out to their customers using the same date format, setting this as text just made it easier (well until the occasional dates switched to US defaults).



Regards

J.

 

[SkipVought]

You do realize that an ACTUAL date can be FORMATTED to DISPLAY in any number of different formats, including DMY, while your TEXT structure is virtually worthless for analysis and calculations.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JasonEnsor]

Hi Skip,

I am aware that it can be formatted to show how the user would like it. This has been a project that has been building for the last 12 months and I thought it would be easier to do it this way than to go back over all my code and relook at how I handle the date. The more I think about it though the more I think that I am going to be better off going back and accepting Excels default then format the data onscreen to the user's requirements. This project was started when I had not so much experience in vba and the user wanted a paper based system moved to an electronic system (that functioned the same as the paper based system), but gave little in the way of specifications....until halfway through development when they thought of functionalities that they required.

I think it is going to be a long weekend of re-evaluating code

Regards

J.

 

[SkipVought]

        With ws.Cells(lNextRow, "c")
            .NumberFormat = "dd mm yyyy" 'this is a DATE format
            .Value = DateSerial(2013, 5, 6)
        End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}