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

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

create column to denote leg#

I need a macro that will make a new output tab with a new column for the trade history section of the original tabs (pm or rm).  Each row is considered a leg.  I need the new column to denote a number for each leg of any spread type and start over with the next symbol.  An example of what this might look like is shown here in the third column >> http://screencast.com/t/WF0hnvqi8pon

Note that the third column is not there by default and is created based on the spread.  If it is a multi-legged spread, the column shows the number of legs.  If it is a single-legged spread, it only shows one leg.
trade-macro-v2.1c.xlsm
0
rtod2
Asked:
rtod2
  • 4
  • 3
1 Solution
 
rspahitzCommented:
Could you use a formula instead?  Something like this? (starting in cell C2)


=IF(AND(B2<>"",B2<>0),"Leg1","Leg"&IF(C1="",1,(VALUE(MID(C1&"   0",4,3))+1)))
0
 
rtod2Author Commented:
That certainly doesn't create an output tab.  Macro still needed.
0
 
rspahitzCommented:
sorry, I guess I don't understand your request.
if you create a new tab on the workbook, what do you want in it?

VBA code to create a new tab:

Sub AddOutputSheet()
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "Output"
End Sub
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rtod2Author Commented:
Ah, ok
Yeah I was wanting it to extract the trade history section and add a new row to the trade history.
0
 
rspahitzCommented:
so you're looking to create a new tab called "rm output" that does the same thing that pm output does?
At this point, I don't see the correlation between pm and pm-output so I wouldn't know how to make the rm output's data.

It seems you may need to break this down into smaller tasks (possibly with separate questions) such as:
1) search through the rm sheet looking for TYPE TRD in column C and place the corresponding value into a new tab called rm output
2) add a new column C in rm output called leg and populate it with "LEG" followed by a number there the number starts at 1 and increases by 1 every time the corresponding value in column B is blank

etc.
0
 
rtod2Author Commented:
Yeah, just looking for it to extrapolate that section from either pm or rm and create the new column.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

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