Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting and sorting cells with VBA
I need to apply date formatting to column A and then sort the entire
worksheet by column A ascending. I don't know how many rows/columns there will be, but row 1 is always the header row. I calculate the last row and column like this: lc = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row (no problem using SpecialCells here) I've tried the following VBA code, but no luck: With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range("A2:A" & lr).NumberFormat = "mm/dd/yyyy hh:mm;@" End with With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range(.Cells(2, 1), .Cells(lr, lc)).NumberFormat = "mm/dd/yyyy hh:mm;@" End with Why is this not working? The macro recorder returns this: Range("A2:A106").Select Selection.NumberFormat = "mm/dd/yyyy hh:mm;@" Do I need to select the range in VBA?? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting and sorting cells with VBA
Are there any errors with the following:
With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range("A2:A" & lr).Select End with or With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range(.Cells(2, 1), .Cells(lr, lc)).Select End with If you have errors with the above then you are not identifying the ranges correctly. "news.sf.sbcglobal.net" wrote: I need to apply date formatting to column A and then sort the entire worksheet by column A ascending. I don't know how many rows/columns there will be, but row 1 is always the header row. I calculate the last row and column like this: lc = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row (no problem using SpecialCells here) I've tried the following VBA code, but no luck: With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range("A2:A" & lr).NumberFormat = "mm/dd/yyyy hh:mm;@" End with With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range(.Cells(2, 1), .Cells(lr, lc)).NumberFormat = "mm/dd/yyyy hh:mm;@" End with Why is this not working? The macro recorder returns this: Range("A2:A106").Select Selection.NumberFormat = "mm/dd/yyyy hh:mm;@" Do I need to select the range in VBA?? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting and sorting cells with VBA
Are there any errors with the following:
With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range("A2:A" & lr).Select End with or With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range(.Cells(2, 1), .Cells(lr, lc)).Select End with If you have errors with the above then you are not identifying the ranges correctly. news.sf.sbcglobal.net is me - deko... Thanks for the reply - I got it working... but now I have other problems. If the column is formatted as text, it appears correctly in the chart X-axis labels - but after I format as date, only numbers (row numbers) appear in the X-axis labels. Any idea why this would be? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting and sorting cells with VBA
no need to use select. your code should work. that is.. if xlapp is a valid excel instance and .workbooks(strXLSfile) is a valid workbook and .worksheets(sn(i)) is a valid worksheet. and lr is a number between 1 and 2^16 and lc is a number between 1 and 2^8 also note that when you use automation it is best to avoid with .. end with syntax if you have problems quitting the instantiated excel that may be the cause. it maybe better to use object variables which you can check in the locals window.. AND can explicitly set to nothing when done. or combined with a few diagnostic msgboxes :) dim wkb as object dim wks as object set wkb = workbooks(strxlsfile) if wkb is nothing then msgbox "oops" set wks = wkb.worksheets(sn(i)) if wks is nothing then msgbox "oops" if lr<2 or lr 2^16 then msgbox "oops again" wks.range("a2:a" & lr).numberformat = "dd/mm" wks.usedrange.sort wks.cells(1), 1, header:=1 set wks = nothing wkb.save set wkb = nothing xlapp.quit set xlapp = nothing -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam news.sf.sbcglobal.net wrote : I need to apply date formatting to column A and then sort the entire worksheet by column A ascending. I don't know how many rows/columns there will be, but row 1 is always the header row. I calculate the last row and column like this: lc = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row (no problem using SpecialCells here) I've tried the following VBA code, but no luck: With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range("A2:A" & lr).NumberFormat = "mm/dd/yyyy hh:mm;@" End with With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range(.Cells(2, 1), .Cells(lr, lc)).NumberFormat = "mm/dd/yyyy hh:mm;@" End with Why is this not working? The macro recorder returns this: Range("A2:A106").Select Selection.NumberFormat = "mm/dd/yyyy hh:mm;@" Do I need to select the range in VBA?? Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting and sorting cells with VBA
if xlapp is a valid excel instance
and .workbooks(strXLSfile) is a valid workbook and .worksheets(sn(i)) is a valid worksheet. and lr is a number between 1 and 2^16 and lc is a number between 1 and 2^8 10-4 also note that when you use automation it is best to avoid with .. end with syntax if you have problems quitting the instantiated excel that may be the cause. Now that's something I've not heard before. it maybe better to use object variables which you can check in the locals window.. AND can explicitly set to nothing when done. I've purposely avoided using object variables because that is known to cause problems (in automation scenarios) of Excel not quitting. I did get it working, but the problem is that now that the cells are formatted as dates, the labels in my chart (created from the same data) do not appear correctly. I get row numbers instead of the nicely formatted string I had before. Still troubleshooting... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting and sorting cells with VBA
My code broke again - or perhaps it was never fixed :)
But I think I know what's going on. I'm using a query to populate a series of worksheets, like this: strSql = "SELECT * INTO [Excel 8.0;Database=" & strXlsPath & "].[" & _ strSheetName & strTestType & "] FROM tblExcelData;" db.Execute strSql, dbFailOnError The field in tblExcelData that contains the date is text field (there is a reason for this). For some reason, the cell in the worksheets contains this: '10/12/04 07:20 While the Access table field contains only this: 10/12/04 07:20 Why the leading single quote? How did it get there and how do I get rid of it? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting and sorting cells with VBA
By the way, the reason for the text field (rather than date) is because the
chart that is created from this data gets it X-axis labels from the column with the date values - and only the date (10/12/2004) shows in the X-axis rather than the date and time (10/12/2004 13:23) when the columns is formatted as date. With a string, I get the time as well as the date. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting and sorting cells with VBA
will repost this as new question...
"deko" wrote in message m... By the way, the reason for the text field (rather than date) is because the chart that is created from this data gets it X-axis labels from the column with the date values - and only the date (10/12/2004) shows in the X-axis rather than the date and time (10/12/2004 13:23) when the columns is formatted as date. With a string, I get the time as well as the date. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Cells with Conditional Formatting | Excel Discussion (Misc queries) | |||
Date Formatting & Sorting | Excel Discussion (Misc queries) | |||
Sorting with Formatting | Excel Discussion (Misc queries) | |||
Formatting Date for Sorting | Excel Discussion (Misc queries) | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |