rtod2
asked on
Add column and change Exp column format
The output tab is the result of the Macro below. Unfortunately, the Exp column shown is not reflecting the Exp month and year correctly because it was originally a CSV file. I need the macro used to create the output tab expanded to correct the Exp column to show an abbreviated month and four(4) digit year (i.e. Mar 2011). I also need the addition of another empty column entitled Position. Assistance is greatly appreciated.
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
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
TradeHist-2.3a.xlsm
ASKER
The Position# column should appear just before the Strategy# column.
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(xlD own).Offse t(0, 11))
ActiveWorkbook.Worksheets( strSheetna me & "_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
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
Set wks = ActiveSheet
strSheetname = wks.Name
Set rngSrc = wks.Cells.Find("Account Trade History")
Set rngSrc = wks.Range(rngSrc, rngSrc.End(xlDown).End(xlD
ActiveWorkbook.Worksheets(
Set wks = ActiveWorkbook.Worksheets.
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""
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
Application.DisplayAlerts = True
End Sub
ASKER
All the cells in the Exp column should be four (4) digit years and there should not be a dash, only a space. The Position column should say Position# and should appear just before the Strategy# column. My mistake with the column name Position#.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Almost. A few of the expiration months still do not show a four (4) digit year. I have not yet figured out why some appear to be showing a date. I think just eliminate an actual date where there is one and use the month and year only.
That is because the date in the source sheet is entered in a format which excel does not recognize as a date
ASKER
Yep, but it is. Should be processed as Mo Year.
Unless the entry is recognized by excel as a date it cannot be processed.
ASKER
That's a show stopper for sure. Not sure how to handle that because the next question involves combining those columns (Symbol, Exp, Strike, Type) horizontally.
ASKER
Those column values should show in a single cell as a cohesive unit on which no calculations or sorting will occur.
The proper way is to go back to the source data and correct the faulty data.
If there are hundreds of data to be corrected then maybe come correction pattern can be worked out.
If there are hundreds of data to be corrected then maybe come correction pattern can be worked out.
ASKER
OK, then we can leave it I guess. The source is what it is. I am a user of the product. I have uploaded a CSV file just as it would come out of that product. Is that what you mean? Thank you for helping with this. I'll ask a new question about the combining of the columns on the next post.
2011-03-13-AccountStatement.csv
2011-03-13-AccountStatement.csv
If you look at lines 140 and 193 the Sep5 seems to be a pattern or have some meaning. If this is the case then a formula can be worked out to convert sep5 to sep
ASKER
I see. Thank you for pointing that out. It is a Quarterly which means it should have a specific date instead of just a month and year. So it would be OK to add the extra two (2) digits to the year, a space between the month and the day, and then combine the three (3) columns Exp, Strike, Type leaving the symbol column alone. This should give me enough representative data to work with when trying to define a position number for each type position. If needed, I will gladly pose the question about combining the three (3) columns by itself in another question.
ASKER
Once the Exp issue is corrected as much as it can be with the current data; below is a list of questions that will come next. They are only included here as a placeholder. I will check back in the morning and follow-up with a new question. Thank you very much for your help sir.
COMBINE - Combine only Exp, Strike, Type columns with only a single space between each. (i.e. Mar 2010 45 Call)
WIDTH - Width of columns shoud adjust to contents.
FREEZE - Freeze Column Header Rows
NOTES - The Account Order History (filled and expired items only) precedes the same item in the Account Trade History. What I need are the notes from each of the filled and expired items. The term 'default' in column A of the Account Trade History should be removed but the Order History Notes should appear in the last column instead wrapped if needed so that they will print when the sheet is printed to a PDF.
COMBINE - Combine only Exp, Strike, Type columns with only a single space between each. (i.e. Mar 2010 45 Call)
WIDTH - Width of columns shoud adjust to contents.
FREEZE - Freeze Column Header Rows
NOTES - The Account Order History (filled and expired items only) precedes the same item in the Account Trade History. What I need are the notes from each of the filled and expired items. The term 'default' in column A of the Account Trade History should be removed but the Order History Notes should appear in the last column instead wrapped if needed so that they will print when the sheet is printed to a PDF.
ASKER
Outstaning!
ASKER
New question here >>> https://www.experts-exchange.com/questions/26883418/Notes-needed-also.html
Open in new window