Need help with twice conditioned if statement in vba

G

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
644
Office Version
  1. 365
Platform
  1. Windows
Code: 
If Prov = "BC" And 0.15 * W < 2000 Then LCP = 0.15 * 2000 Else: LCP = 2000
If Prov = "MB" And 0.15 * W < 1800 Then LCP = 0.15 * 2001 Else: LCP = 1800
If Prov = "NB" And 0.2 * W < 2000 Then LCP = 0.2 * 2000 Else: LCP = 2000
If Prov = "NL" And 0.2 * W < 2000 Then LCP = 0.2 * 2000 Else: LCP = 2000
If Prov = "NS" And 0.2 * W < 2000 Then LCP = 0.2 * 2000 Else: LCP = 2000
If Prov = "ON" And 0.05 * W < 375 Then LCP = 0.05 * 375 Else: LCP = 375
If Prov = "SK" And 0.2 * W < 1000 Then LCP = 0.2 * 1000 Else: LCP = 1000
If Prov = "YT" And 0.25 * W < 1250 Then LCP = 0.25 * 1250 Else: LCP = 1250
If Prov = "AB" Then LCP = 0
If Prov = "NU" Then LCP = 0
If Prov = "PE" Then LCP = 0
If Prov = "QC" Then LCP = 0
If Prov = "OC" Then LCP = 0
For the other provinces/territories I have involved, this is the code I wrote. Keep in mind each has their own conditions to follow, but for the one below I have no clue how to do this correctly. I'm hoping you can help.
The following is a notation or direction of how the formula should work. This is for the Northwest Territories (NT) of Canada.
Code: 
LCP  = The lesser of: 
  (i) $29,250; and 
  (ii) 15% of the first $5,000 and 30% of the remainder of amounts deducted or withheld during the year 
for the acquisition by the employee of approved shares of the capital stock of a prescribed labour-
sponsored venture capital corporation.

-- g
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
I'm thinking something like this...

Code: 
If Deducted > 5000 then 
 retval = (5000 * .15) + ((Deducted - 5000) * .30) 
Else
 retval = (Deducted * .15)
End If 
LCP = WorsheetFunction.Min(29250, retval)
 
Upvote 0
Thank you for your assistance. I'm sorry for the late reply.
I initially had trouble understanding what you were trying to do with "retval".
I understand it now but where you have the Else part of the code, how can i include the condition where it only applies to the Prov code of "NT"?

-- g
 
Upvote 0
Wrap the whole thing in "If", if the entire thing is NT only.
I'm not sure it makes sense to qualify the Else with a Providence as the Else applies to the value of the deductions.

Code: 
If Prov = "NT" Then 
 If Deducted > 5000 then 
   retval = (5000 * .15) + ((Deducted - 5000) * .30) 
 Else
  retval = (Deducted * .15)
 End If 
 LCP = WorsheetFunction.Min(29250, retval)
End If 'Prov = "NT"
 
Upvote 0
tweedle said:
Wrap the whole thing in "If", if the entire thing is NT only.
I'm not sure it makes sense to qualify the Else with a Providence as the Else applies to the value of the deductions.

Code: 
If Prov = "BC" And 0.15 * W < 2000 Then LCP = 0.15 * 2000 Else: LCP = 2000If Prov = "MB" And 0.15 * W < 1800 Then LCP = 0.15 * 2001 Else: LCP = 1800If Prov = "NB" And 0.2 * W < 2000 Then LCP = 0.2 * 2000 Else: LCP = 2000If Prov = "NL" And 0.2 * W < 2000 Then LCP = 0.2 * 2000 Else: LCP = 2000If Prov = "NS" And 0.2 * W < 2000 Then LCP = 0.2 * 2000 Else: LCP = 2000If Prov = "ON" And 0.05 * W < 375 Then LCP = 0.05 * 375 Else: LCP = 375If Prov = "SK" And 0.2 * W < 1000 Then LCP = 0.2 * 1000 Else: LCP = 1000If Prov = "YT" And 0.25 * W < 1250 Then LCP = 0.25 * 1250 Else: LCP = 1250If Prov = "AB" Then LCP = 0If Prov = "NU" Then LCP = 0If Prov = "PE" Then LCP = 0If Prov = "QC" Then LCP = 0If Prov = "OC" Then LCP = 0</PRE>If Prov = "NT" Then 
 If Deducted > 5000 then 
   retval = (5000 * .15) + ((Deducted - 5000) * .30) 
 Else
  retval = (Deducted * .15)
 End If 
 LCP = WorsheetFunction.Min(29250, retval)
End If 'Prov = "NT"
Click to expand...
It will be important as all other provinces (MB, BC, QC, etc) will be selected in the one cell.
If W (deducted) is deducted from any of the provinces above I need to make each individual equation work. Sorry I'm probably not explaining this well.

-- g
 
Upvote 0
These are the Canadian provinces I'm working with

Code: 
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=64>[FONT=Calibri]AB[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64>[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64>[FONT=Calibri]Alberta[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]BC[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]British Columbia[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]MB[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]Manitoba[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]NB[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]New Brunswick[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]NL[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]Newfoundland[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]NS[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]Nova Scotia[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]NT[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]Northwest Territories[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]NU[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]Nunavut[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]ON[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]Ontario[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]PE[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]Prince Edward Island[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]SK[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]Saskatchewan[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]YT[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]Yukon Territories[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]OC[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri],[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Calibri]Outside Canada[/FONT]</TD></TR></TBODY></TABLE>
I'm in the middle of writing code that will calculate the tax applied to an individual's salary per pay period.

Code: 
$2,000 of approved shares of the capital stock of a prescribed labour-sponsored venture capital 
corporation

This value is "W"

"LCP" will be the final number in this calculation.

Each province has its own formula based on the value of W and its maximum amount for LCP (as I tried to show in my first post :) )
Code: 
If Prov = "BC" And 0.15 * W < 2000 Then LCP = 0.15 * 2000 Else: LCP = 2000
If Prov = "MB" And 0.15 * W < 1800 Then LCP = 0.15 * 2001 Else: LCP = 1800
If Prov = "NB" And 0.2 * W < 2000 Then LCP = 0.2 * 2000 Else: LCP = 2000
If Prov = "NL" And 0.2 * W < 2000 Then LCP = 0.2 * 2000 Else: LCP = 2000
If Prov = "NS" And 0.2 * W < 2000 Then LCP = 0.2 * 2000 Else: LCP = 2000
If Prov = "ON" And 0.05 * W < 375 Then LCP = 0.05 * 375 Else: LCP = 375
If Prov = "SK" And 0.2 * W < 1000 Then LCP = 0.2 * 1000 Else: LCP = 1000
If Prov = "YT" And 0.25 * W < 1250 Then LCP = 0.25 * 1250 Else: LCP = 1250
If Prov = "AB" Then LCP = 0
If Prov = "NU" Then LCP = 0
If Prov = "PE" Then LCP = 0
If Prov = "QC" Then LCP = 0
If Prov = "OC" Then LCP = 0


All of these work except for the Northwest Territories
which is

Code: 
LCP = The lesser of: 
  (i) $29,250; and 
  (ii) 15% of the first $5,000 and 30% of the remainder of amounts 
deducted or withheld for the purchase of approved shares.
300 is to number I'm looking for here but any code I've tried so far has not work.

-- g
 
Upvote 0
This returns LCP = 300 based on W = 2000

Code: 
Sub FooProof()
Prov = "NT"
W = 2000
If Prov = "NT" Then
 If W > 5000 Then
   retval = (5000 * 0.15) + ((W - 5000) * 0.3)
 Else
  retval = (W * 0.15)
 End If
LCP = WorksheetFunction.Min(29250, retval)
End If 'Prov = "NT"
MsgBox LCP
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Index Match results = WTF?
2
Replies
17
Views
2K
gcefaloni
G
R
  • Question Question
Help!
Replies
3
Views
2K
63falcondude
63falcondude
G
  • Question Question
Code is out of order. Running 3 times to get final total. Need help correcting.
Replies
2
Views
504
greegan
G

Forum statistics

Threads
1,224,531
Messages
6,179,379
Members
452,907
Latest member
Roland Deschain

Share this page

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