Nested If in VBA

[Karthikeyan Devan]

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.

 

[meldoc]

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]

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]

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>

 

[Karthikeyan Devan]

Sorry Brian!! Let me check this code and will get back to you shortly.