What is wrong with that code

[greg_em]

Dim tbl As ListObject
Dim columns_names() As Variant
columns_names = Array("Nr referencyjny", "Data efektywna", "Data księgowania", "Strona operacji", "Waluta", "Kwota", "Kod SWIFT", "Nr ref. Klienta", _
"Opis", "GVC", "Kod operacji", "Kod operacji", "Tytuł operacji", "Kontrahent", "Nazwa skrócona kontrahenta", "Adres kontrahenta", "Adres kontrahenta 2", _
"ID banku kontrahenta", "ID rachunku kontrahenta", "Rachunek kontrahenta", "Kod rekoncyliacji", "Główny nr referencyjny")

Set tbl = current_sheet.ListObjects.Add(xlSrcRange, Range("A6"), xlYes)

For i = 0 To UBound(columns_names)
    If i = 0 Then
        tbl.ListColumns(tbl.ListColumns.Count).Name = columns_names(i)
        Else
        tbl.ListColumns.Add
        tbl.ListColumns(tbl.ListColumns.Count).Name = columns_names(i)
    End If
Next i

current_sheet.ListObjects("tbl").ListColumns("Nr referencyjny").DataBodyRange.NumberFormat = "@"
' current_sheet is an Sub argument

' OR

tbl.ListColumns("Data efektywna").DataBodyRange.Style = "Short Date"

' OR

Set tbl.ListColumns("Data księgowania").DataBodyRange.Style = "Short Date"

I'm always getting "subscription out of range" or "object not set" errors.
I was seen some code examples, where ListColumns object is assigned to variable, and then style or NumberFormat is applying to variable, like:

Dim v as ListColumn
Set v as tbl.ListColumns("Nr referencyjny")
v.DataBodyRange.NumberFormat = "@"

but is that really needed?

Why it is necessary to use extra variable and I cannot simply refer to tbl.ListColumns("Column_header")?
Or it is possible, and I'm doing something wrong?

 

[DanteAmor]

When you only have one row in the table it sends an error.

Try:

  tbl.ListColumns("Nr referencyjny").Range.NumberFormat = "@"
  tbl.ListColumns("Data efektywna").Range.NumberFormat = "d/m/yyyy"
  tbl.ListColumns("Data ksiegowania").Range.NumberFormat = "d/m/yyyy"

 

[Alex Blakenburg]

Dante's solution will resolve your immediate issue but Tables are a bit quirky.
If you are manually going to be entering data and relying on the table to auto expand, I suggest that you format the entire column "Nr referencyjny" as Text (not just the table part) before putting in the table (still formatting the table as Dante has suggested). If you don't do that when you enter a new number manually under the table it will format as text but be recognised by Excel as a number which will get terribly confusing. (If you want to test it enter a number with leading zeroes, it will drop the zeroes when it goes in as a number)
If you add the row to the table first then enter the number it will work fine.

Also if you want to get rid of the For/Next loop you could replace that with the 2 lines in blue below:

Set tbl = Sheet1.ListObjects.Add(xlSrcRange, Range("A6"), xlYes)
tbl.Resize tbl.Range.Resize(, UBound(columns_names) + 1)
tbl.HeaderRowRange.Value = columns_names

 

[patricktoulon1]

hello
an authers way from
you complete the simply range and transform to listobject
next in select case you put a spécial format to entire column

Dim tbl As ListObject
    Dim col As Object
    Dim columns_names() As Variant
    columns_names = Array("Nr referencyjny", "Data efektywna", "Data ksiegowania", "Strona operacji", "Waluta", "Kwota", "Kod SWIFT", "Nr ref. Klienta", _
                           "Opis", "GVC", "Kod operacji", "Kod operacji", "Tytul operacji", "Kontrahent", "Nazwa skrócona kontrahenta", "Adres kontrahenta", "Adres kontrahenta 2", _
                           "ID banku kontrahenta", "ID rachunku kontrahenta", "Rachunek kontrahenta", "Kod rekoncyliacji", "Glówny nr referencyjny")

    current_sheet.[A6].Resize(1, UBound(columns_names)+1) = columns_names

    Set tbl = current_sheet.ListObjects.Add(xlSrcRange, [$A$6].Resize(1, UBound(columns_names)+1), , xlYes)
    tbl.Name = "Tableau4"
    For Each col In tbl.ListColumns
        Select Case col.Name
            Case "Data efektywna": col.Range.NumberFormat = "dd-mm-yy"
              
            Case "Data ksiegowania": col.Range.NumberFormat = "dd-mm-yy"
              
                'etc.....
        End Select
    Next

you can add an case Else
example
case else : col.range.numberformat="@"