VBA Project

hashim217

New Member
Joined
Aug 8, 2011
Messages
11
Hi all,

I have a project I am working on and need some guidance as to how I should approach this. What I need is a macro that will check a file that I have that contains a SYMBOL and a certain quantity for Long and Short next to it.

Example:

Symbol____________________Long____Short
<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=211 border=0 x:str><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7716" width=211><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 158pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=211 height=17>4SPX110812C01355000__________200_______25</TD></TR></TBODY></TABLE>
I need to grab the symbol and look it up in another master file which has the data listed as follows:

Account#_________Symbol_________________Quantity______L/S
<TABLE style="WIDTH: 63pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=84 border=0 x:str><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 63pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=84 height=17>033184359__________4SPX110812C01355000_________200___________L</TD></TR></TBODY></TABLE>
033184359__________4SPX110812C01355000_________25____________S


What I need is to take the Symbol from the original file and look it up in the master file and take the total of all the Long positions and Short positions for that symbol in the master file and see if it matches the original file. If it does not, I need some indication that it does not match (Red Highlighting). To add some more complexity to the situation, I need it to only add the Long & Short values for accounts that do NOT begin with "07".

I am not looking for someone to do my work for me. If you provide me with some bread crumbs that I can follow and give me some direction it would be greatly appreciated. I am new to using VBA and do not even know how I would approach this. Thank you all for your time.


-Hash
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi Hash,

You don't need a macro to accomplish this. You can use a SUMPRODUCT formula, which will work across closed workbooks.

With your Master File.xls file open, paste this formula into D3, adjust for your sheet names and data ranges then copy over to E3 and down for all your rows....

=SUMPRODUCT(--('[Master File.xlsx]Sheet1'!$C$2:$C$1000),--('[Master File.xlsx]Sheet1'!$B$2:$B$1000=$A3),--('[Master File.xlsx]Sheet1'!$D$2:$D$1000=D$2),--(LEFT('[Master File.xlsx]Sheet1'!$A$2:$A$1000,2)<>"07"))

Excel Workbook
ABCDE
1Sum From MasterSum From Master
2SymbolLongShortLS
34SPX110812C013550002002520025
Original File Sheet
 
Upvote 0

Forum statistics

Threads
1,224,561
Messages
6,179,522
Members
452,923
Latest member
JackiG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top