Adding a column to a table
 

I'm trying to add a column to a table in excel using vba. Here is the code I
have.

Dim tblData As ListObject
Dim calendarWeek As ListColumn

tblData = Sheets("Sheet1").ListObjects("Table1") <- Error Here
calendarWeek = tblTop8Issue.ListColumns.Add
calendarWeek.Name = "Calendar Week"
calendarWeek.DataBodyRange = "WEEKNUM([Date])"

I get an error on the first line saying Invalid Use of Property...Please help

Adding a column to a table
 

Since you're trying to set the variable equal to an object, I believe you
need to use the SET statement, such as:

Set tblData = Sheets("Sheet1").ListObjects("Table1")

Try that out...

--
Happy calculating!

If you like this answer, please click ''Yes.''




"Brad" wrote:

I'm trying to add a column to a table in excel using vba. Here is the code I
have.

Dim tblData As ListObject
Dim calendarWeek As ListColumn

tblData = Sheets("Sheet1").ListObjects("Table1") <- Error Here
calendarWeek = tblTop8Issue.ListColumns.Add
calendarWeek.Name = "Calendar Week"
calendarWeek.DataBodyRange = "WEEKNUM([Date])"

I get an error on the first line saying Invalid Use of Property...Please help

Adding a column to a table
 

that worked great, thanks. Getting an error now on the last line
calendarWeek.DataBodyRange = "=WEEKNUM([Date])"
any ideas?

"GSnyder" wrote:

Since you're trying to set the variable equal to an object, I believe you
need to use the SET statement, such as:

Set tblData = Sheets("Sheet1").ListObjects("Table1")

Try that out...

--
Happy calculating!

If you like this answer, please click ''Yes.''




"Brad" wrote:

I'm trying to add a column to a table in excel using vba. Here is the code I
have.

Dim tblData As ListObject
Dim calendarWeek As ListColumn

tblData = Sheets("Sheet1").ListObjects("Table1") <- Error Here
calendarWeek = tblTop8Issue.ListColumns.Add
calendarWeek.Name = "Calendar Week"
calendarWeek.DataBodyRange = "WEEKNUM([Date])"

I get an error on the first line saying Invalid Use of Property...Please help

Adding a column to a table
 

Are you looking for the current week number? If so, weeknum(now()) may work
instead. I don't think it'll recognize [Date].

--
Happy calculating!

If you like this answer, please click ''Yes.''




"Brad" wrote:

that worked great, thanks. Getting an error now on the last line
calendarWeek.DataBodyRange = "=WEEKNUM([Date])"
any ideas?

"GSnyder" wrote:

Since you're trying to set the variable equal to an object, I believe you
need to use the SET statement, such as:

Set tblData = Sheets("Sheet1").ListObjects("Table1")

Try that out...

--
Happy calculating!

If you like this answer, please click ''Yes.''




"Brad" wrote:

I'm trying to add a column to a table in excel using vba. Here is the code I
have.

Dim tblData As ListObject
Dim calendarWeek As ListColumn

tblData = Sheets("Sheet1").ListObjects("Table1") <- Error Here
calendarWeek = tblTop8Issue.ListColumns.Add
calendarWeek.Name = "Calendar Week"
calendarWeek.DataBodyRange = "WEEKNUM([Date])"

I get an error on the first line saying Invalid Use of Property...Please help

Adding a column to a table
 

Actually I had to type out the whole thing like

=WEEKNUM(Table1[#ThisRow],[Date])

thanks for all the help

"GSnyder" wrote:

Are you looking for the current week number? If so, weeknum(now()) may work
instead. I don't think it'll recognize [Date].

--
Happy calculating!

If you like this answer, please click ''Yes.''




"Brad" wrote:

that worked great, thanks. Getting an error now on the last line
calendarWeek.DataBodyRange = "=WEEKNUM([Date])"
any ideas?

"GSnyder" wrote:

Since you're trying to set the variable equal to an object, I believe you
need to use the SET statement, such as:

Set tblData = Sheets("Sheet1").ListObjects("Table1")

Try that out...

--
Happy calculating!

If you like this answer, please click ''Yes.''




"Brad" wrote:

I'm trying to add a column to a table in excel using vba. Here is the code I
have.

Dim tblData As ListObject
Dim calendarWeek As ListColumn

tblData = Sheets("Sheet1").ListObjects("Table1") <- Error Here
calendarWeek = tblTop8Issue.ListColumns.Add
calendarWeek.Name = "Calendar Week"
calendarWeek.DataBodyRange = "WEEKNUM([Date])"

I get an error on the first line saying Invalid Use of Property...Please help