VBA / Macro Conditional -> Adding rows and capturing data

[RickyWJohnson]

Hello All,

Any input I can gather relating to this is greatly appreciated. I am moderately skilled in Excel and poor at VBA (just bought a book and will be learning in the near future).

Background: This project is related to the tracking of dividend payments.
I have two seperate workbooks. WorkbookA contains all of the relevant information for dividends that should have paid (identifier,ex_date,pay_date,etc). WorkbookB is a quasi database that I've assembled and contains all of our holdings, broken down by account and position within each worksheet. Each worksheet is denoted by a different date (numeric form of date). I've compiled the data up to this point.

What I am looking to do illustrated in example tables:

WorkBookA

A B C D E F
1 Dividend Identifier Ex_Date_Numeric Account Position TypeA TypeB
2 44444 912010 RickyAccount 5 Cash Div
3 88888 952010 JessicaAccount 9 Stock Div
4 12222 9142010 GuestAccount 7 Cash Div


WorkBookB

Sheet Title: 912010

A B C
1 Security Identifier Account Position
2 444444 RickyAccount 5
3 444444 GuestAccount 9
4 444444 JessicaAccount 7
5 88888 JessicaAccount 4
6 88888 GuestAccount 4
7 12222 GuestAccount 1


WorkBookA has a Vlookup formula which will find the first security identifier in WorkBookB within the correct sheet based on the Ex_Date of the dividend. This is how the first account and holdings is pulled on WorkBookA. I am looking to write a script which enables me to do the following: go back and search the appropriate worksheet within WorkBookB for a second, third, fourth, etc; account that may hold the security (IE: Right now, I am only able to pull data for holdings of 444444 in Ricky's account, the first accout found with my Vlookup formula). If there is a second account which holds the security in WorkBookB and the appropriate date sheet, insert a new row in WorkBookA below the first security, return the account name of that second account from WorkBookB and Sheet, return the holdings of that second account from WorkBookB, and copy the same data from columns E through F above it in WorkBookA. If not, move on to the next security. With this script, the finished table in WorkBookA would look as follows:

A B C D E F
1 Dividend Identifier Ex_Date_Numeric Account Position TypeA TypeB
2 44444 912010 RickyAccount 5 Cash Div
3 44444 912010 GuestAccount 9 Cash Div
4 44444 912010 JessicaAccount 7 Cash Div
5 88888 952010 JessicaAccount 4 Stock Div
6 88888 952010 GuestAccount 4 Stock Div
7 12222 9142010 GuestAccount 1 Cash Div


Thanks for any time spent looking at this!!!!!

[davesexcel]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!