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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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>
 
Upvote 0
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!!!
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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
Back
Top