| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Table Column
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a Table Column
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.NumberFor mat = "mm/dd/yyyy" -- If this post helps click Yes --------------- Jacob Skaria "Steve Haack" wrote: 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a Table Column
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.NumberFor mat = "mm/dd/yyyy" -- If this post helps click Yes --------------- Jacob Skaria "Steve Haack" wrote: 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a Table Column
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" wrote: 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.NumberFor mat = "mm/dd/yyyy" -- If this post helps click Yes --------------- Jacob Skaria "Steve Haack" wrote: 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a Table Column
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" wrote: 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.NumberFor mat = "mm/dd/yyyy" -- If this post helps click Yes --------------- Jacob Skaria "Steve Haack" wrote: 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting a Table Column
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" wrote: 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.NumberFor mat = "mm/dd/yyyy" -- If this post helps click Yes --------------- Jacob Skaria "Steve Haack" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selecting a Table Column | Excel Programming | |||
| Pivot Table - Selecting the GrandTotal Column in VBA xl2003 | Excel Programming | |||
| Text to column and selecting values based on a different column | Excel Worksheet Functions | |||
| Text to column and selecting values based on a different column | Excel Worksheet Functions | |||
| Text to column and selecting values based on a different column | Excel Discussion (Misc queries) | |||
Linear Mode
