avadhutd2
09-14-2009, 02:59 AM
Hi,
I need a help for formatting the DATE fields in a sheet which appears in General Format.
The situation is as follows -
I am fetching data in columns from DB (Total 55 in DB out of which few or all are selected by the user). There are few date columns (like Arr_DT, Dep_DT etc)
After getting data in Sheet, the cell format for DATES is seen as general. For example - We can see date as 38500 instead of 5/28/05. I tried following code, but most of the columns are getting converted to DATE format.
Cells.Find(What:="_DT", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _, SearchFormat:=False).Activate
ActiveCell.Columns.EntireColumn.Select
Selection.NumberFormat = "mm/dd/yy ;@"
For i = 0 To ListBox1.ListCount
' This is the count of columns selected by USER. i.e This many times I need to iterate & see whether there are any columns having "_DT" text.
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Columns.EntireColumn.Select
Selection.NumberFormat = "mm/dd/yy ;@"
Next
What I need is ...if user selects 25 columns & there are 3 DATE columns, then using this code we should be able to identify these 3 DATE fields & then format only those columns.
......In short only DATE fields should be identified and formatted & OTHERS should be remaining in the same format as got from DB.
Currently I am getting some wrong results...Can anyone guide me here or propose any other alternative to acheive this requirement?
Thanks!
I need a help for formatting the DATE fields in a sheet which appears in General Format.
The situation is as follows -
I am fetching data in columns from DB (Total 55 in DB out of which few or all are selected by the user). There are few date columns (like Arr_DT, Dep_DT etc)
After getting data in Sheet, the cell format for DATES is seen as general. For example - We can see date as 38500 instead of 5/28/05. I tried following code, but most of the columns are getting converted to DATE format.
Cells.Find(What:="_DT", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _, SearchFormat:=False).Activate
ActiveCell.Columns.EntireColumn.Select
Selection.NumberFormat = "mm/dd/yy ;@"
For i = 0 To ListBox1.ListCount
' This is the count of columns selected by USER. i.e This many times I need to iterate & see whether there are any columns having "_DT" text.
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Columns.EntireColumn.Select
Selection.NumberFormat = "mm/dd/yy ;@"
Next
What I need is ...if user selects 25 columns & there are 3 DATE columns, then using this code we should be able to identify these 3 DATE fields & then format only those columns.
......In short only DATE fields should be identified and formatted & OTHERS should be remaining in the same format as got from DB.
Currently I am getting some wrong results...Can anyone guide me here or propose any other alternative to acheive this requirement?
Thanks!