Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

Add column for primary key.

I need to add a primary key column labeled PK right before the empty Pos# column.  The PK# column should create static numbers, centered horizontally, for each row starting with 1 at the top and ascending toward the bottom.  Since the initial order of items is crucial, this primary key will serve as a basis for sorting the sheet moving forward.  
Option Explicit

Public Sub CopyTradeHistory()
  Dim wks As Worksheet
  Dim rngSrc As Range
  Dim strSheetname As String
  Dim lastrow As Long
  Dim Cell As Variant
  
  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 = "Spread#"
  wks.Cells(2, 4).Value = "Spread"
  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
  For Each Cell In wks.Range("I3:I" & lastrow)
    With Cell
        
            .Value = DateSerial(2000 + Day(.Value), Month(.Value), 1)
            .NumberFormat = "mmm yyyy"
       
    End With
  Next Cell
  wks.Columns(3).Insert
  wks.Cells(2, 3).Value = "Pos#"
  With wks.Range("P3:P" & lastrow)
    .Formula = "=text(J3,""mmm yyyy"") & "" "" & K3 & "" "" & L3"
    .Value = .Value
    .Copy
    wks.Range("J3:J" & lastrow).PasteSpecial
    wks.Range("J2").Value = "Instrument"
    Application.CutCopyMode = False
    .ClearContents
    '.Formula = "=I3 & E3"
    '.Value = .Value
  End With
  
  
  wks.Range("K:L").Delete shift:=xlToLeft
  wks.Range("B1").Value = wks.Range("A1").Value
  wks.Range("A:A").Delete shift:=xlToLeft
  wks.Columns("A:M").EntireColumn.AutoFit
  wks.Columns("B:C").HorizontalAlignment = xlCenter
  wks.Columns("G:G").HorizontalAlignment = xlCenter
  wks.Columns("D:D").HorizontalAlignment = xlJustify
  wks.Rows("3:3").Select
  ActiveWindow.FreezePanes = True
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

Open in new window

TradeHist-2.4a.xlsm
0
rtod2
Asked:
rtod2
  • 6
  • 4
  • 3
1 Solution
 
Rory ArchibaldCommented:
Try this:
Public Sub CopyTradeHistory()
  Dim wks As Worksheet
  Dim rngSrc As Range
  Dim strSheetname As String
  Dim lastrow As Long
  Dim Cell As Variant
  
  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 = "Spread#"
  wks.Cells(2, 4).Value = "Spread"
  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
  For Each Cell In wks.Range("I3:I" & lastrow)
    With Cell
        
            .Value = DateSerial(2000 + Day(.Value), Month(.Value), 1)
            .NumberFormat = "mmm yyyy"
       
    End With
  Next Cell
  wks.Columns(3).Insert
  wks.Cells(2, 3).Value = "Pos#"
  With wks.Range("P3:P" & lastrow)
    .Formula = "=text(J3,""mmm yyyy"") & "" "" & K3 & "" "" & L3"
    .Value = .Value
    .Copy
    wks.Range("J3:J" & lastrow).PasteSpecial
    wks.Range("J2").Value = "Instrument"
    Application.CutCopyMode = False
    .ClearContents
    '.Formula = "=I3 & E3"
    '.Value = .Value
  End With
  wks.Columns(3).Insert
  wks.Cells(2, 3).Value = "PK"
  With wks.Range("C3:C" & lastrow)
   .Value = Evaluate("INDEX(ROW(" & .Address & ")-2,0)")
   .NumberFormat = "0"
   .HorizontalAlignment = xlHAlignCenter
   End With
  wks.Range("K:L").Delete shift:=xlToLeft
  wks.Range("B1").Value = wks.Range("A1").Value
  wks.Range("A:A").Delete shift:=xlToLeft
  wks.Columns("A:M").EntireColumn.AutoFit
  wks.Columns("B:C").HorizontalAlignment = xlCenter
  wks.Columns("G:G").HorizontalAlignment = xlCenter
  wks.Columns("D:D").HorizontalAlignment = xlJustify
  wks.Rows("3:3").Select
  ActiveWindow.FreezePanes = True
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

Open in new window

0
 
rtod2Author Commented:
0
 
rtod2Author Commented:
This answer caused the Instrument column to be removed.  I also need an I/S# if possible.  If the Instrument and the Symbol are the same on another row, then they should both have the same I/S number.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
MakriniCommented:
TradeHist-2-4b.xlsm

This resolves the problem and adds the i/s#
0
 
rtod2Author Commented:
Almost!

Row 134 and 145 for instance would have the same I/S#.  The horizontal and vertical spacing is no longer adjusting to contents and the I/S# should be named that at the top of the column.
0
 
rtod2Author Commented:
It is the instrument AND the symbol that will make up the I/S#
0
 
Rory ArchibaldCommented:
@rtod2
The "Hire Me" button on a member's profile is there if you want to pay for work to be done on contract, not to draw attention to your questions! ;)
0
 
rtod2Author Commented:
Actually, you are a top expert.  Let's talk about that.  I am looking to make a web app out of the final.
0
 
rtod2Author Commented:
Actually, you are a top expert.  Let's talk about that.  I am looking to make a web app out of the final.  Note that I am only responding.  For mods,  I am not offering payment.
0
 
Rory ArchibaldCommented:
Web apps are not my area, I'm afraid, whether you are paying or not.
0
 
MakriniCommented:
Not only is Rorya a top expert, he is one of the most amazing Excel and VBA gurus I have ever come across.  I learn from his posts all the time
0
 
Rory ArchibaldCommented:
Ta very much! I shall spend the rest of the day feeling bigheaded now. :)
0
 
MakriniCommented:
lol - you should.  I don't think I've been to a forum where you aren't one of the most prevalent
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now