Nested If in VBA

Karthikeyan Devan

Board Regular
Joined
May 5, 2009
Messages
114
Hi All,

This is a very good site for Excel and i have got copule of automation here. Again, i am here where i need your help on Nested if which we can have in VBA.

Please find below screen shot:

<TABLE style="WIDTH: 143pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=191 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20> </TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=67>A</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 45pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=60>B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Customer</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">XYZ</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Service</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Intercity</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">System</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Siebel</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Price</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$200 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Discount</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

1st criteria:

Now, if customer is XYZ, service is Intercity, System is Siebel and Price is $200 then in discount cell (blank cell), i need the discount price (50%) which is calculated based on the price (b4).

2nd Criteria:

if customer is XYZ, service is Metro, System is Siebel and Price is $200 then in discount cell (blank cell), i need the discount price (40%) which is calculated based on the price (b4).

3rd Criteria:

if customer is ZZZ, service is Intercity, System is Siebel and Price is $200 then in discount cell (blank cell), i need the discount price (60%) which is calculated based on the price (b4).

4th Criteria:

if customer is ZZZ, service is Metro, System is Siebel and Price is $200 then in discount cell (blank cell), i need the discount price (80%) which is calculated based on the price (b4).

Please help with me on this and trust me i could save lots of time if it can be automated.

Any help would be greatly appreciated.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

meldoc

Well-known Member
Joined
Jul 18, 2009
Messages
1,249
Hello

Try.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()<br>  s = Range("B1") & Range("B2") & Range("B3")<br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> s<br>      <SPAN style="color:#00007F">Case</SPAN> "XYZIntercitySiebel"<br>        Disc = 50<br>      <SPAN style="color:#00007F">Case</SPAN> "XYZMetroSiebel"<br>        Disc = 40<br>      <SPAN style="color:#00007F">Case</SPAN> "ZZZIntercitySiebel"<br>        Disc = 60<br>      <SPAN style="color:#00007F">Case</SPAN> "ZZZMetroSiebel"<br>        Disc = 80<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>  Range("B5") = Range("B4") * Disc / 100<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Karthikeyan Devan

Board Regular
Joined
May 5, 2009
Messages
114
Hi Mel

THanks a lot...

There are two other different customer namely ABC and DCE.

If user choose ABC in cell B1 and Intercity in cell B2 then a Msgbox has to display stating: "Refer ABC excelsheet".


If user choose DCE in cell B1 and Intercity in cell B2 then a Msgbox has to display stating: "Refer DCE excelsheet".

Any help on this would be greatly appreciated!!!
 

meldoc

Well-known Member
Joined
Jul 18, 2009
Messages
1,249
Hello again

Please call me Brian. :)

Replace the code from last time with both the subs below.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test2()<br>  s = Range("B1") & Range("B2") & Range("B3")<br>  Range("B5") = ""<br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> s<br>      <SPAN style="color:#00007F">Case</SPAN> "XYZIntercitySiebel"<br>        Disc = 50<br>        <SPAN style="color:#00007F">Call</SPAN> calc(Disc)<br>      <SPAN style="color:#00007F">Case</SPAN> "XYZMetroSiebel"<br>        Disc = 40<br>        <SPAN style="color:#00007F">Call</SPAN> calc(Disc)<br>      <SPAN style="color:#00007F">Case</SPAN> "ZZZIntercitySiebel"<br>        Disc = 60<br>        <SPAN style="color:#00007F">Call</SPAN> calc(Disc)<br>      <SPAN style="color:#00007F">Case</SPAN> "ZZZMetroSiebel"<br>        Disc = 80<br>        <SPAN style="color:#00007F">Call</SPAN> calc(Disc)<br>      <SPAN style="color:#00007F">Case</SPAN> "ABCIntercitySiebel"<br>        MsgBox "Refer ABC excelsheet"<br>      <SPAN style="color:#00007F">Case</SPAN> "DCEIntercitySiebel"<br>        MsgBox "Refer DCE excelsheet"<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> calc(Disc)<br>  Range("B5") = Range("B4") * Disc / 100<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,718
Messages
5,833,289
Members
430,202
Latest member
Faizal5zl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top