Date question in Excel

[GFAlexander]

I have a range of data where one column contains a date in british format dd/mm/yyyy. If I sub-total the range of data based on the date then the sub-total below is displayed correctly. I.E. 01/08/2004 Total.

However if I record exactly the same keystrokes in a macro and run the macro, what I get displayed is 8/1/2004 Total (US date format).

Anyone know a reason why or fix for this?

Thanx

Gary

 

[xlbo]

It's because VBA defaults dates to US standard - you would need to set the formatting on the subtotal cells back to dd/mm/yyyy via the FORMAT function in VBA

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[GFAlexander]

Hi Geoff,

Thanx for the answer. Being a bit of a novice to VBA (I do most of what I need by recording a macro), can you tell me what syntax I would need to use?

Thanx

Gary

 

[xlbo]

Please post the code you have

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[GFAlexander]

Hi Geoff,

Here is the entire code for the macro, not sure if you wanted it all or not.

Thanx again

Gary

Sub EPROSS()
'
' EPROSS Macro
' Macro recorded 17/06/2004 by Gary Alexander
'

'
Application.WindowState = xlMaximized
Workbooks.OpenText Filename:="C:\accview\Tmp\EPROSS.TXT", Origin:=xlMSDOS, _
StartRow:=10, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array( _
11, 1), Array(22, 4), Array(30, 1), Array(71, 1), Array(77, 4), Array(85, 1), Array(91, 1), _
Array(104, 1), Array(117, 1), Array(130, 1)), TrailingMinusNumbers:=True
Cells.Select
Selection.Columns.AutoFit
ActiveWindow.LargeScroll Down:=1
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll Down:=39
ActiveWindow.LargeScroll ToRight:=-1
Cells.Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWindow.SmallScroll ToRight:=4
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.LargeScroll ToRight:=-1
Columns("A:A").Select
Selection.Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range("C:C,E:E").Select
Range("E1").Activate
Selection.EntireColumn.Hidden = True
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Cells.Select
Selection.Subtotal GroupBy:=6, Function:=xlSum, TotalList:=Array(10), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=6, Function:=xlCount, TotalList:=Array(10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=3
ActiveWindow.SmallScroll ToRight:=5
Selection.ColumnWidth = 13
ActiveWindow.SmallScroll ToRight:=3
Cells.Select
Range("F2").Activate
Selection.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Cells.Select
' Selection.Replace What:=" Total", Replacement:="", LookAt:=xlPart, _
' SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
' ReplaceFormat:=False
ActiveWindow.SmallScroll ToRight:=3
End Sub

 

[xlbo]

Well - looks like you have all dates in column F - that being the case, you could just use

Range("F2:F" & activesheet.usedrange.rows.count).numberformat = "dd/mm/yyyy")

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question