Hey guys,
I have this 'Top-50' chart, that I'm trying to convert to a dynamic one sorta speak, so that it can rank different things at the push of a button. It looks like this:
Attachment 559576
As you can see near the top, I have two series of buttons:
- One that would allow the user to select "Général", "Positif" and "Négatif"
- One that would allow the user to switch from 1-50 and 51-100, therefor converting the Top-50 to a Top-100.
I already have the 1-50/51-100 covered. Basically, the VBA Macro only changes the first number (ie 1 to 51) and since all the numbers under are just an offset +1, they all follow. Simple enough. 
The left part though, not sure how to go about it.
Bascially, I need it to re-write (or modify) the INDEX/MATCH formulas in cells DV14:DV38 and GX14:GX38, which returns the stock's names (ie. CGI Group, Open Text, Facebook, etc.) according to which ranking system is being used.
Each ranking system has its own column in our hidden DATA table, and are labeled TRNK, POSRNK, NEGRNK.
If you take a look at 'CGI Group' in the picture above (which displays our 'Général' ranking system), the formula is:
So it basically looks at the number 5 cells to its left (worksheet's cell had to be heavily merged due to layout issues), takes that number (in this case, 1) and look for that exact number in DATA[TRNK] where all our stocks are ranked, according to the 'Général' formula...then returns DATA[TITRES].
Both 'POSITIF' and 'NÉGATIF' would work the exact same way, but referring to a different column than DATA[TRNK]. So bascially:
Thing is, slowly learning VBA on my own, I'm not sure how to properly build a code to do that yet, as I've never attempted to modify a formula via VBA before.
Someone willing to help me get going ?
...would very appreciated any help !
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks