We need two VB macros coded in XLS 2003.
MACRO 1: Find Missing Payees Macro: In one XLS there is a tab called Payee details. There is another tab called Releases. sheet. This macro needs to print out a list of missing Payee records. To do this it needs to work down every cell in the Column E Releases until there are no more entries. Work down Colum E (in RecCalc), in each cell read the “Payee Code”, then do a search for it in the Payee Code column ( C ) on the Payees tab. If it exists there do nothing and move onto the next cell. If it doesn't exist then list that missing Payee code in a text file that pops up. There should be just 1 text file with all the missing Payee codes in there. One missing Payee code on each line of the text file.
MACRO 2: Each line in the Payees tab is someone who needs to get paid. If you look in Payees tab Columns E,F,G it says whether if the Payee in that row is expected to be due income from that source, indicated by a “Yes” or “No”. This macro needs to search the three Calc tabs (RecCalc, PubCalc and CompCalc) for mentions of the Payee Code for that row. When it finds a mention in of the Payee Code any of these three tabs then it needs to print various bits of information into a text file. All the information in that text file will be relating to that Payee. There will be one reference on each line of the text file. The text file needs to be displayed. When I am working on the data this helps me see that everything is linked.
Each of the Calc sheets has various columns the Payee Code may appear in. In the RecCalc tab it is Colum E. In the CompCalc tab it is Colum F. In the PubCalc sheet it is either column K,N,Q, T,W,Z. All 3 Calc tabs need to be searched for Payee Codes (even if it that relevant tab flag is indicates a “no”).
When a Payee code is identified in one of the Calc tabs then various fields need to be printed out onto the line in the text file.
IF the Payee Code is found on the RecCalc sheet print the content from the following cells on that line: Cat (A), Artist (B), Release (C)
IF the Payee Code is found on the CompCalc sheet print the content from the following cells on that line: Cat (A), Artist (B), Track (C)
IF the Payee Code is found on the PubCalc sheet print the content from the following cells on that line: ID (A), Comp (B), Song (C), MainRec Artist (AO)
ALSO optionally write “MISMATCH” at the end of that line IF the PayeeCode appears on the Calc Page and the Yes / No flag for that page type was set to “No”. (For example If Payee Comp (F) column has a “No” BUT that Payee IS found in the Comp tab then print MISMATCH). This will tell me to mark that column to “Yes”.
Hi, as agreed, I'm bidding for this first phase. I assessed it at $20. Let's keep each phase separate so we can monitor, evaluate, and manage the project easier. Good to work with you again.--francis