Solved

Add column for primary key.

Posted on 2011-03-14
14
256 Views
Last Modified: 2012-05-11
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
Comment
Question by:rtod2
  • 6
  • 4
  • 3
14 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35130307
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
 

Author Comment

by:rtod2
ID: 35130489
0
 

Author Comment

by:rtod2
ID: 35134170
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
 
LVL 10

Accepted Solution

by:
Makrini earned 500 total points
ID: 35134383
TradeHist-2-4b.xlsm

This resolves the problem and adds the i/s#
0
 

Author Comment

by:rtod2
ID: 35134499
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
 

Author Comment

by:rtod2
ID: 35134503
It is the instrument AND the symbol that will make up the I/S#
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35135693
@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
 

Author Comment

by:rtod2
ID: 35135752
Actually, you are a top expert.  Let's talk about that.  I am looking to make a web app out of the final.
0
 

Author Comment

by:rtod2
ID: 35135758
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35136132
Web apps are not my area, I'm afraid, whether you are paying or not.
0
 
LVL 10

Expert Comment

by:Makrini
ID: 35136141
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35136157
Ta very much! I shall spend the rest of the day feeling bigheaded now. :)
0
 
LVL 10

Expert Comment

by:Makrini
ID: 35136175
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now