VBA If Else Help

DalPai

New Member
Joined
Aug 13, 2018
Messages
22
Hello everyone!


I have a "problem" in a spreadsheet that I believe through VBA I could solve. But I never programmed in VBA, so I have no idea where to start.

I have a table with the following information: Project Name, Type, Stage, Value, Adjusted Value.

The adjusted value depends on the stage the project is.
ex: If a project of value 100 is in the Idea stage, its adjusted value is 100*10% = 10

I was able to solve this simply by using =IF in excel.

However, the percentage will also change according to the Project type. And writing such a formula using =IF would be huge.


I believe that by VBA I can do this easily, but where to start?

Best Regards
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
How many project types are there?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,117
Office Version
365
Platform
Windows
You may be able to accomplish what you want without VBA. You can set up a lookup table that has your various Projects and Stages and associated value, and then you can use an Index/Match formula to look up the appropriate value from that table.
See here: https://spreadsheeto.com/index-match/
 

DalPai

New Member
Joined
Aug 13, 2018
Messages
22
Hi Fluff,

There are 7 types, but the % changes only with one Type.

Type: Core
Stage 0: 0%
Stage 1: 15%
Stage 2: 25%
Stage 3: 40%
Stage 4: 50%
Stage 5: 100%

Type: Other 6
Stage 0: 0%
Stage 1: 5%
Stage 2: 20%
Stage 3: 40%
Stage 4: 90%
Stage 5: 100%
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
Ok, how about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Project Name</td><td style=";">Type</td><td style=";">Stage</td><td style=";">Value</td><td style=";">Adjusted Value</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Core</td><td style=";">Stage 0</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">Core</td><td style=";">Stage 1</td><td style="text-align: right;;">100</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style=";">Core</td><td style=";">Stage 2</td><td style="text-align: right;;">100</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style=";">Core</td><td style=";">Stage 3</td><td style="text-align: right;;">100</td><td style="text-align: right;;">40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style=";">Core</td><td style=";">Stage 4</td><td style="text-align: right;;">100</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">Core</td><td style=";">Stage 5</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">??</td><td style=";">Stage 0</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">??</td><td style=";">Stage 1</td><td style="text-align: right;;">100</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style=";">??</td><td style=";">Stage 2</td><td style="text-align: right;;">100</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">??</td><td style=";">Stage 3</td><td style="text-align: right;;">100</td><td style="text-align: right;;">40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style=";">??</td><td style=";">Stage 4</td><td style="text-align: right;;">100</td><td style="text-align: right;;">90</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style=";">??</td><td style=";">Stage 5</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td></tr></tbody></table><p style="width:1.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">RC</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=D2*IF(<font color="Blue">B2="Core",CHOOSE(<font color="Red">RIGHT(<font color="Green">C2,1</font>)+1,0,0.15,0.25,0.4,0.5,1</font>),CHOOSE(<font color="Red">RIGHT(<font color="Green">C2,1</font>)+1,0,0.05,0.2,0.4,0.9,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

DalPai

New Member
Joined
Aug 13, 2018
Messages
22
You may be able to accomplish what you want without VBA. You can set up a lookup table that has your various Projects and Stages and associated value, and then you can use an Index/Match formula to look up the appropriate value from that table.
See here: https://spreadsheeto.com/index-match/
I completely forgot about the INDEX/MATCH formula.

Worked like a charm. Thank you very much
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,117
Office Version
365
Platform
Windows
You are welcome.
Glad we were able to help.
 

Forum statistics

Threads
1,089,636
Messages
5,409,460
Members
403,264
Latest member
naturally_data

This Week's Hot Topics

Top