Run time error 438 object doesnt support this property or method

[vbacoder12]

Hi ,
I cannot copy my code into the message at this time. I am going to type n i really apologize. When using code I get the error in the subject. Also it puts the table in a style which is not in the code. Debug highlights set tbl line. Which starts set tbl and ends at 21) if this is too hard to read it can upload image. If I can

Sub FormatATable()
Dim tbl As ListObject
Dim rng As Range
Set rng = Range("A6") Settbl=ActiveSheet.ListObjects.Add(xlSrcRange, Range("A6:Q9000"), , xlYes).Header = No.TableStyle=Green Table Style Medium 21)
End Sub

 

[coulombevin]

Hi vbacoder12,

Please provide a screenshot, also, you have a closing parenthesis after 21 but no openning one why?

Bests regards,

Vincent

 

[Alex Blakenburg]

I can't find a Green 21 but this will give you the syntax:

Sub FormatATable()
    Dim tbl As ListObject
    Dim rng As Range
    
    Set rng = Range("A6")
    Set tbl = ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=Range("A6:Q9000"), _
                                            XlListObjectHasHeaders:=xlYes, TableStyleName:="TableStyleMedium25")

End Sub

 

[vbacoder12]

I can't find a Green 21 but this will give you the syntax:

Sub FormatATable()
    Dim tbl As ListObject
    Dim rng As Range
   
    Set rng = Range("A6")
    Set tbl = ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=Range("A6:Q9000"), _
                                            XlListObjectHasHeaders:=xlYes, TableStyleName:="TableStyleMedium25")

End Sub

This very useful. But what would I add if I wanted my first line of data to be headers. That would be row 6. Also how could I add borders around the cells

 

[Alex Blakenburg]

That code specifies the range starting at row 6 and for Headers = xlYes, which makes row 6 the headings. Is that not working ?

If you want to add borders ideally you need to find a style that does what you want or copy and modify a style to suit. Then use the style name of the custom style in the code.

 

[vbacoder12]

That code specifies the range starting at row 6 and for Headers = xlYes, which makes row 6 the headings. Is that not working ?

If you want to add borders ideally you need to find a style that does what you want or copy and modify a style to suit. Then use the style name of the custom style in the code.

It added headers above row 6. My headers start at row 6 and data at row 7

 

[Alex Blakenburg]

I put some data on Row 5 and it still did the right thing and put the header row on Row 6
Show me the exact code you are using and a screenshot of the sheet showing the row and column references.

 

[vbacoder12]

I put some data on Row 5 and it still did the right thing and put the header row on Row 6
Show me the exact code you are using and a screenshot of the sheet showing the row and column references.

Now I see where I made the mistake at i have data in row 6 (names for headers already). I just want to make that data in row 6 the headers

 

[Alex Blakenburg]

Do you have any merged rows in your spreadsheet that start at Row 5 ?

 

[vbacoder12]

Do you have any merged rows in your spreadsheet that start at Row 5 ?

No