nirvehex
06-29-2015, 11:12 AM
Hey guys,
I'm trying to figure out some VBA code to test the following:
In column AB I have a drop down list that has the following 4 choices, "New Equipment", "Rent Equipment", "Preventative Maintenance", and "No Change". These generate an outcome in column AS. The values are in rows AB2:to End of sheet-1 row (I have a total in the last row). Same thing with column AS. AS is a percentage figure (ROI).
What I'm trying to do is see which choice in column AB generates the highest number in column AS and then keep that selection in column AB as the active selection.
Essentially the macro should do the following for each row in column AB from AB2 to end of rows -1.
Set AB2 = "New Equipment"
What's the value in AS2?
Set AB2 = "Rent Equipment"
What's the value in AS2?
Set AB2 = "Preventative Maintenance"
What's the value in AS2?
Set AB2 = "No Change"
What's the value in AS2?
Which value in AB2 generated the highest number in AS2? Set AB2 to the value that generated the highest number in AS2.
Repeat this from row 2 down to end -1 row.
Any ideas?
Thanks.
Also this is cross posted in slightly different wording at http://www.mrexcel.com/forum/excel-questions/864709-visual-basic-applications-do-what-if-testing.html
I'm trying to figure out some VBA code to test the following:
In column AB I have a drop down list that has the following 4 choices, "New Equipment", "Rent Equipment", "Preventative Maintenance", and "No Change". These generate an outcome in column AS. The values are in rows AB2:to End of sheet-1 row (I have a total in the last row). Same thing with column AS. AS is a percentage figure (ROI).
What I'm trying to do is see which choice in column AB generates the highest number in column AS and then keep that selection in column AB as the active selection.
Essentially the macro should do the following for each row in column AB from AB2 to end of rows -1.
Set AB2 = "New Equipment"
What's the value in AS2?
Set AB2 = "Rent Equipment"
What's the value in AS2?
Set AB2 = "Preventative Maintenance"
What's the value in AS2?
Set AB2 = "No Change"
What's the value in AS2?
Which value in AB2 generated the highest number in AS2? Set AB2 to the value that generated the highest number in AS2.
Repeat this from row 2 down to end -1 row.
Any ideas?
Thanks.
Also this is cross posted in slightly different wording at http://www.mrexcel.com/forum/excel-questions/864709-visual-basic-applications-do-what-if-testing.html