Selecting a Table Column

  • Thread starter Thread starter Steve Haack
  • Start date Start date
S

Steve Haack

I have several worksheets (more then 50). Each has a table that has several
coumns of data. Each table is named with the name of a site, such as
"tblSite1".

In code, I wan to go through each of the tables, select a specific column
called RunTime and change it format to a date.

what I am trying to do is this (psuedo code):
Set wkshtSiteWorksheet = ActiveWorkbook.Worksheets(strSiteName)
Set rngBootStatTable = wkshtSiteWorksheet.Range( _
strTableName & "[RunTime]")
rngBootStatTable.NumberFormat = "m/d/yyyy"

It fails on setting the RunTime column range into rngBootStatTable

Can someone tell me why this does not work, and how to properly reference a
Column that is defined in an excel table?
 
Dear Steve

Do you mean to find the column header "Runtime" and change the number format
of the entire colum ...then try the below ..

lngCol = Rows(1).Cells.Find(What:="Runtime", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set wkshtSiteWorksheet = ActiveWorkbook.Worksheets(strSiteName)
wkshtSiteWorksheet.Columns(lngCol).Cells.NumberFormat = "mm/dd/yyyy"
 
Jacob,
Yes that is what I mean to do. One thing though, perhaps I wasn't clear
enough. On each worksheet, I have "defined" a table by inserting a table via
a query from access and then renaming that table to match the site name.

When I use Excel functions, I am able to refer to the entire column as

tblSite1[RunTime] , for example.

Why am I not also able to do that in VB?

I will give your suggestion a try...

Steve

Jacob Skaria said:
Dear Steve

Do you mean to find the column header "Runtime" and change the number format
of the entire colum ...then try the below ..

lngCol = Rows(1).Cells.Find(What:="Runtime", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set wkshtSiteWorksheet = ActiveWorkbook.Worksheets(strSiteName)
wkshtSiteWorksheet.Columns(lngCol).Cells.NumberFormat = "mm/dd/yyyy"

--
If this post helps click Yes
---------------
Jacob Skaria


Steve Haack said:
I have several worksheets (more then 50). Each has a table that has several
coumns of data. Each table is named with the name of a site, such as
"tblSite1".

In code, I wan to go through each of the tables, select a specific column
called RunTime and change it format to a date.

what I am trying to do is this (psuedo code):
Set wkshtSiteWorksheet = ActiveWorkbook.Worksheets(strSiteName)
Set rngBootStatTable = wkshtSiteWorksheet.Range( _
strTableName & "[RunTime]")
rngBootStatTable.NumberFormat = "m/d/yyyy"

It fails on setting the RunTime column range into rngBootStatTable

Can someone tell me why this does not work, and how to properly reference a
Column that is defined in an excel table?
Click to expand...
Click to expand...
 
Jacob,

I'm trying to do something similar except my column headers are in "mm/yyyy"
format and I need to select last month's column and copy and paste in
column("BW").

Help please.

TIA

DPingger

Jacob Skaria said:
Dear Steve

Do you mean to find the column header "Runtime" and change the number format
of the entire colum ...then try the below ..

lngCol = Rows(1).Cells.Find(What:="Runtime", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set wkshtSiteWorksheet = ActiveWorkbook.Worksheets(strSiteName)
wkshtSiteWorksheet.Columns(lngCol).Cells.NumberFormat = "mm/dd/yyyy"

--
If this post helps click Yes
---------------
Jacob Skaria


Steve Haack said:
I have several worksheets (more then 50). Each has a table that has several
coumns of data. Each table is named with the name of a site, such as
"tblSite1".

In code, I wan to go through each of the tables, select a specific column
called RunTime and change it format to a date.

what I am trying to do is this (psuedo code):
Set wkshtSiteWorksheet = ActiveWorkbook.Worksheets(strSiteName)
Set rngBootStatTable = wkshtSiteWorksheet.Range( _
strTableName & "[RunTime]")
rngBootStatTable.NumberFormat = "m/d/yyyy"

It fails on setting the RunTime column range into rngBootStatTable

Can someone tell me why this does not work, and how to properly reference a
Column that is defined in an excel table?
Click to expand...
Click to expand...
 
Back
Top