rtod2
asked on
Correct date format in macro
Attached is a macro and sheet containing an output tab. The macro output tab processes most of the dates correctly as they are formatted by an abbreviated month, followed by a four(4) digit year (i.e. Mar 2011). This is precisely correct. Some types of trades though have an actual date that is important also and so the formatting is not correct for those. In the cases where there is an actual date in the Exp column (some of these are denoted in yellow), the format for them should be an abbreviate month, followed by a two (2) digit date, followed by a four (4) digit year (i.e. Mar 5 2011) with no commas or punctuation. Assistance is greatly appreciated. Both the macro and sheet are here.
Option Explicit
Public Sub CopyTradeHistory()
Dim wks As Worksheet
Dim rngSrc As Range
Dim strSheetname As String
Dim lastrow As Long
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wks = ActiveSheet
strSheetname = wks.Name
Set rngSrc = wks.Cells.Find("Account Trade History")
Set rngSrc = wks.Range(rngSrc, rngSrc.End(xlDown).End(xlDown).Offset(0, 11))
ActiveWorkbook.Worksheets(strSheetname & "_output").Delete
Set wks = ActiveWorkbook.Worksheets.Add
wks.Name = strSheetname & "_output"
rngSrc.Copy wks.Range("A1")
wks.Columns(3).Insert
wks.Columns(5).Insert
wks.Cells(2, 3).Value = "Strategy#"
wks.Cells(2, 4).Value = "Strategy"
wks.Cells(2, 5).Value = "Leg#"
wks.Rows("1:2").Font.Bold = True
lastrow = wks.Range("a" & 64000).End(xlUp).Row
wks.Range("E3:E" & lastrow).Formula = "=IF(OR(H3<>H2,E2=""leg2""),""Leg1"",""Leg2"")"
wks.Range("E3:E" & lastrow).Value = wks.Range("E3:E" & lastrow).Value
wks.Range("C3:C" & lastrow).Formula = "=counta(B3:B$3)"
wks.Range("C3:C" & lastrow).NumberFormat = "General"
wks.Range("C3:C" & lastrow).Value = wks.Range("C3:C" & lastrow).Value
wks.Range("I3:I" & lastrow).NumberFormat = "mmm yyyy"
wks.Columns(3).Insert
wks.Cells(2, 3).Value = "Position#"
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
TradeHist-2.3b.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have re-examined this need and think it is a bad idea after all at this point. Thanks for the assistance. Here is the new question >> https://www.experts-exchange.com/questions/26883875/Expand-macro-to-combine-columns.html
ASKER