Hello all!
Attached to this thread is a base file that contains some formulas that I've made, and thanks to gn00588950, a very comprehensive Macro. My goal is to get this sheet to be free of user input--and therefore, user error. The file is a very rough sketch, lacking any major formatting or fluff. It is using a type of formula that your computer can't run, these are Bloomberg terminal commands =BDP(...). Don't worry about whether or not they necessarily run, I'm more concerned with the macros.
The first sheet is the Messaging sheet. This is where the user works. The user will input identifiers into Column B starting at Row 8. When finished they will hit a "Run Macro" button that will run a Macro saved in the workbook as "SuperMacro."
SuperMacro should do all that is in VBA you currently see, when its done it should call a new macro
------------------------------
NewMacro1 (NM1)
NM1 will copy the identifiers from Column B of "Messaging" and paste them into A8 of "Category Table." It will then insert a formula into B8, and drag that formula to the bottom (filldown or Selection.Autofill?)
This will be the same formula as those in SuperMacro except it will reference cell B6 from 'Category Table.'
It then insert a VLookup formula, and drag to bottom, to pull the correct codes from the Cross Reference table on the same sheet.
IDK if this would be a part of the same macro or not but somehow, these values need to be pasted into F, G, H, and I on 'Messaging' I'm thinking have the macro perform another Vlookup, using the identifiers from Messaging column B and treat 'Category Table' A8:Dx as a table, pulling in values for columns 3 (F, H), and 4 (G, I).
------------------------------
------------------------------
NewMacro2 (NM2)
NM2 will use column T as a refrence for more formulas, this would be =TEXT(Tx,"mm") and =TEXT(Tx,"dd") for columns V and W, respectively.
------------------------------
NewMacro3 (NM3)
NM3 will copy the identifiers from Messaging "B" to 'Effective Maturity' A8. It will then insert another formula and drag it to the bottom as it did in NM1. The macro must then evaluate the output of that formula, if the output = 100 (exactly 100) then it needs to insert a formula into columns F and G (another BDP formula, just like it did for Column B) of that row where Bx = 100. It must also insert the IF(ISBLANK(ISERROR))) formula into H and the IF() formula into I, for those respective rows. It also needs to get the formulas for J and K into those same rows.
Once all this is done, NM3 must capture the the instances where an identifier has an output in colums J and K, and insert those values into the appropriate row in 'Messaging" Z and AA.
------------------------------
NewMacro4 (NM4)
Must take the identifiers from 'Messaging' B, and insert them at 'Name' A8. It must also insert the correct formulas all the way through column K and drag them down. Once finished it has to take the output from Column K and insert it into the correct row on 'Messaging' ABx.
------------------------------
NewMacro5 (NM5)
This macro I see as possibly not even necessary, the idea is to convert all the formulas on Messaging into values, so I imagine a Select.PasteSpecial on A8:ABx.
------------------------------
When its done and every line of code has run, it should give a prompt saying "Process Complete!"
I would like it to where the user only needs to click a single button, and all this will run automatically, so when SuperMacro has run through all its code (I guess when Bx = "") it calls NM1, so on and so forth. I know its a lot, thank you to anyone offering their expertise.