Help with VBA Code IF then or IfElse

K

kasey217

New Member
Joined
Jun 18, 2003
Messages
30
I have a document I had the if and then statements in excel but I was messing them up yearly because it is based on years of service. I want to do it as VBA, so it will calculate automatically without having to do anything.
The columns I have are YTD salary, hire date, the year (to calculate the exact number of years of service) it all 7/1/2018, years of service and LG Pay

The conditions are:

If someone has 5 -9.9 years they get the salary (*) .02
If someone has 10-14.9 years they get the salary (*) .03
If someone has 15 and above they get the salary (*) .04.

Can someone help me create VB Code to to make the column LG pay calculate automatically. I appreciate your help.

Thank you
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
you can also use SELECT Statement.

Code: 
Sub YTDSalary()
Dim oSalary As Currency, revised_Salary
Dim oYear As Long


oYear = 6
oSalary = 50000
revised_Salary = oSalary
Select Case oYear
    
    Case 5 To 9.9
        revised_Salary = oSalary * 0.02
    Case 10 To 14.9
        oSalary = oSalary * 0.03
    Case Is > 14.9
        revised_Salary = oSalary * 0.04
    End Select
MsgBox revised_Salary + oSalary


End Sub
 
Upvote 0
here is syntax for IF statement.

Code: 
[h=2]Syntax[/h][COLOR=#000000][FONT=&quot]The syntax for the IF-THEN-ELSE statement in Microsoft Excel is:[/FONT][/COLOR]
If condition_1 Then
   result_1

ElseIf condition_2 Then
  result_2

...

ElseIf condition_n Then
   result_n

Else
   result_else

End If
 
Upvote 0
MUKESHY12390 said:
you can also use SELECT Statement.

Code: 
Sub YTDSalary()
Dim oSalary As Currency, revised_Salary
Dim oYear As Long


oYear = 6
oSalary = 50000
revised_Salary = oSalary
Select Case oYear
    
    Case 5 To 9.9
        revised_Salary = oSalary * 0.02
    Case 10 To 14.9
        oSalary = oSalary * 0.03
    Case Is > 14.9
        revised_Salary = oSalary * 0.04
    End Select
MsgBox revised_Salary + oSalary


End Sub

Thank you for your help, but I am getting a box that comes up now with 51000 and that is it. I did have to rename the field LG_Pay to Revised_salary.  I can send you the spreadsheet if you can help?
Thanks again for your help.
Click to expand...
 
Upvote 0
This one looks like it would work, but I don't know what to start with in VB . I think I could get it if you could tell me what to start with. This is what my spreadsheet looks like:

YTD Salary Hire date Yr Yrs of Svc Revised Salary
36,000 7/14/2014 7/1/2018 3.97
75,705 3/1/2018 7/1/2018 0.33
28,500 1/1/2018 7/1/2018 0.50
36,000 1/1/2018 7/1/2018 0.50
27,053 1/1/2018 7/1/2018 0.50
22,847 1/1/2018 7/1/2018 0.50
27,053 11/1/2017 7/1/2018 0.66
24,580 11/1/2017 7/1/2018 0.66
25,132 11/1/2017 7/1/2018 0.66
25,132 11/1/2017 7/1/2018 0.66
45,835 10/1/2017 7/1/2018 0.75
27,053 10/1/2017 7/1/2018 0.75
27,053 10/1/2017 7/1/2018 0.75
41,200 8/1/2017 7/1/2018 0.92
25,886 6/1/2017 7/1/2018 1.08
25,132 6/1/2017 7/1/2018 1.08
30,894 10/1/2016 7/1/2018 1.75
59,225 6/1/2016 7/1/2018 2.08
27,053 4/1/2016 7/1/2018 2.25
25,071 4/1/2016 7/1/2018 2.25
27,053 4/1/2016 7/1/2018 2.25
35,000 2/1/2016 7/1/2018 2.41
47,334 2/1/2016 7/1/2018 2.41
28,500 11/1/2015 7/1/2018 2.67
29,175 10/1/2015 7/1/2018 2.75
27,053 10/1/2015 7/1/2018 2.75
33,418 9/1/2015 7/1/2018 2.83
28,500 8/1/2015 7/1/2018 2.92
37,662 8/1/2015 7/1/2018 2.92
28,644 8/1/2015 7/1/2018 2.92
29,175 7/1/2015 7/1/2018 3.00
28,500 7/1/2015 7/1/2018 3.00
32,626 6/1/2015 7/1/2018 3.08
53,045 6/1/2015 7/1/2018 3.08
28,500 5/1/2015 7/1/2018 3.17
33,418 5/1/2015 7/1/2018 3.17
27,053 5/1/2015 7/1/2018 3.17
28,500 4/1/2015 7/1/2018 3.25
36,000 3/1/2015 7/1/2018 3.34
33,418 3/1/2015 7/1/2018 3.34
29,175 2/1/2015 7/1/2018 3.41
30,236 2/1/2015 7/1/2018 3.41
27,053 2/1/2015 7/1/2018 3.41
36,000 9/1/2014 7/1/2018 3.83
27,053 7/1/2014 7/1/2018 4.00
28,644 7/1/2014 7/1/2018 4.00
36,000 1/1/2014 7/1/2018 4.50
29,147 11/1/2013 7/1/2018 4.67
54,285 9/1/2013 7/1/2018 4.83
31,827 8/1/2013 7/1/2018 4.92
37,662 7/1/2013 7/1/2018 5.00
33,418 7/1/2013 7/1/2018 5.00
39,253 6/1/2013 7/1/2018 5.08
36,000 6/1/2013 7/1/2018 5.08
44,558 6/1/2013 7/1/2018 5.08
36,000 3/1/2013 7/1/2018 5.34
28,500 2/1/2013 7/1/2018 5.41
40,314 1/1/2013 7/1/2018 5.50
44,558 10/1/2012 7/1/2018 5.75
36,000 9/1/2012 7/1/2018 5.83
28,500 11/1/2017 7/1/2018 6.00
40,314 7/1/2012 7/1/2018 6.00
47,741 7/1/2012 7/1/2018 6.00
28,500 6/1/2012 7/1/2018 6.08
38,961 5/1/2012 7/1/2018 6.17
34,735 5/1/2012 7/1/2018 6.17
47,741 7/1/2011 7/1/2018 7.01
44,558 6/1/2011 7/1/2018 7.09
28,114 6/1/2011 7/1/2018 7.09
28,777 6/1/2011 7/1/2018 7.09
29,402 4/1/2011 7/1/2018 7.25
39,865 2/1/2011 7/1/2018 7.42
37,132 11/1/2010 7/1/2018 7.67
44,558 9/1/2010 7/1/2018 7.84
45,619 8/1/2010 7/1/2018 7.92
37,636 8/1/2010 7/1/2018 7.92
54,106 5/1/2010 7/1/2018 8.17
43,272 5/1/2010 7/1/2018 8.17
63,654 9/1/2009 7/1/2018 8.84
29,147 9/1/2009 7/1/2018 8.84
36,000 6/1/2009 7/1/2018 9.09
49,230 5/1/2009 7/1/2018 9.17
29,147 4/1/2009 7/1/2018 9.25
42,436 2/1/2009 7/1/2018 9.42
33,418 10/1/2008 7/1/2018 9.75
43,497 8/1/2008 7/1/2018 9.92
55,001 7/1/2008 7/1/2018 10.01
37,918 4/1/2008 7/1/2018 10.25
42,436 4/1/2008 7/1/2018 10.25
58,198 3/1/2008 7/1/2018 10.34
45,449 10/1/2007 7/1/2018 10.76
47,741 5/1/2007 7/1/2018 11.18
31,827 4/1/2007 7/1/2018 11.26
37,547 11/1/2006 7/1/2018 11.67
47,512 10/1/2006 7/1/2018 11.76
50,081 8/1/2006 7/1/2018 11.92
58,774 6/1/2006 7/1/2018 12.09
44,786 5/1/2006 7/1/2018 12.18
37,917 2/1/2006 7/1/2018 12.42
86,199 6/1/2005 7/1/2018 13.09
36,000 6/1/2005 7/1/2018 13.09
37,918 4/1/2005 7/1/2018 13.26
43,497 12/1/2004 7/1/2018 13.59
28,012 10/1/2004 7/1/2018 13.76
32,288 10/1/2004 7/1/2018 13.76
89,116 9/1/2004 7/1/2018 13.84
32,967 8/1/2004 7/1/2018 13.92
28,484 8/1/2004 7/1/2018 13.92
33,418 7/1/2004 7/1/2018 14.01
54,436 8/1/2003 7/1/2018 14.93
53,395 8/1/2003 7/1/2018 14.93
56,228 7/1/2003 7/1/2018 15.01
44,558 5/1/2003 7/1/2018 15.18
75,705 4/1/2003 7/1/2018 15.26
47,741 12/1/2002 7/1/2018 15.59
53,918 1/1/2002 7/1/2018 16.51
30,913 7/1/2001 7/1/2018 17.01
47,512 6/1/2001 7/1/2018 17.09
80,628 9/1/2000 7/1/2018 17.84
43,600 2/1/2000 7/1/2018 18.42
55,236 1/1/2000 7/1/2018 18.51
57,342 8/1/1999 7/1/2018 18.93
37,132 8/1/1999 7/1/2018 18.93
29,044 8/1/1998 7/1/2018 19.93
80,737 6/1/1998 7/1/2018 20.10
29,175 1/1/1998 7/1/2018 20.51
34,479 6/1/1997 7/1/2018 21.10
35,090 3/1/1996 7/1/2018 22.35
55,056 1/1/1996 7/1/2018 22.51
33,203 7/1/1995 7/1/2018 23.02
53,045 8/1/1994 7/1/2018 23.93
43,150 9/1/1993 7/1/2018 24.85
27,053 7/1/1993 7/1/2018 25.02
34,018 12/1/1989 7/1/2018 28.60
58,655 7/1/1986 7/1/2018 32.02
60,415 4/1/1986 7/1/2018 32.27
34,847 11/1/1984 7/1/2018 33.68
60,415 8/1/1983 7/1/2018 34.94
48,695 9/1/1982 7/1/2018 35.85
55,497 7/1/1981 7/1/2018 37.02
 
Upvote 0
kasey217 said:
This one looks like it would work, but I don't know what to start with in VB . I think I could get it if you could tell me what to start with. This is what my spreadsheet looks like:

YTD Salary Hire date Yr Yrs of Svc Revised Salary
36,000 7/14/2014 7/1/2018 3.97
75,705 3/1/2018 7/1/2018 0.33
28,500 1/1/2018 7/1/2018 0.50
36,000 1/1/2018 7/1/2018 0.50
27,053 1/1/2018 7/1/2018 0.50
22,847 1/1/2018 7/1/2018 0.50
27,053 11/1/2017 7/1/2018 0.66
24,580 11/1/2017 7/1/2018 0.66
25,132 11/1/2017 7/1/2018 0.66
25,132 11/1/2017 7/1/2018 0.66
45,835 10/1/2017 7/1/2018 0.75
27,053 10/1/2017 7/1/2018 0.75
27,053 10/1/2017 7/1/2018 0.75
41,200 8/1/2017 7/1/2018 0.92
25,886 6/1/2017 7/1/2018 1.08
25,132 6/1/2017 7/1/2018 1.08
30,894 10/1/2016 7/1/2018 1.75
59,225 6/1/2016 7/1/2018 2.08
27,053 4/1/2016 7/1/2018 2.25
25,071 4/1/2016 7/1/2018 2.25
27,053 4/1/2016 7/1/2018 2.25
35,000 2/1/2016 7/1/2018 2.41
47,334 2/1/2016 7/1/2018 2.41
28,500 11/1/2015 7/1/2018 2.67
29,175 10/1/2015 7/1/2018 2.75
27,053 10/1/2015 7/1/2018 2.75
33,418 9/1/2015 7/1/2018 2.83
28,500 8/1/2015 7/1/2018 2.92
37,662 8/1/2015 7/1/2018 2.92
28,644 8/1/2015 7/1/2018 2.92
29,175 7/1/2015 7/1/2018 3.00
28,500 7/1/2015 7/1/2018 3.00
32,626 6/1/2015 7/1/2018 3.08
53,045 6/1/2015 7/1/2018 3.08
28,500 5/1/2015 7/1/2018 3.17
33,418 5/1/2015 7/1/2018 3.17
27,053 5/1/2015 7/1/2018 3.17
28,500 4/1/2015 7/1/2018 3.25
36,000 3/1/2015 7/1/2018 3.34
33,418 3/1/2015 7/1/2018 3.34
29,175 2/1/2015 7/1/2018 3.41
30,236 2/1/2015 7/1/2018 3.41
27,053 2/1/2015 7/1/2018 3.41
36,000 9/1/2014 7/1/2018 3.83
27,053 7/1/2014 7/1/2018 4.00
28,644 7/1/2014 7/1/2018 4.00
36,000 1/1/2014 7/1/2018 4.50
29,147 11/1/2013 7/1/2018 4.67
54,285 9/1/2013 7/1/2018 4.83
31,827 8/1/2013 7/1/2018 4.92
37,662 7/1/2013 7/1/2018 5.00
33,418 7/1/2013 7/1/2018 5.00
39,253 6/1/2013 7/1/2018 5.08
36,000 6/1/2013 7/1/2018 5.08
44,558 6/1/2013 7/1/2018 5.08
36,000 3/1/2013 7/1/2018 5.34
28,500 2/1/2013 7/1/2018 5.41
40,314 1/1/2013 7/1/2018 5.50
44,558 10/1/2012 7/1/2018 5.75
36,000 9/1/2012 7/1/2018 5.83
28,500 11/1/2017 7/1/2018 6.00
40,314 7/1/2012 7/1/2018 6.00
47,741 7/1/2012 7/1/2018 6.00
28,500 6/1/2012 7/1/2018 6.08
38,961 5/1/2012 7/1/2018 6.17
34,735 5/1/2012 7/1/2018 6.17
47,741 7/1/2011 7/1/2018 7.01
44,558 6/1/2011 7/1/2018 7.09
28,114 6/1/2011 7/1/2018 7.09
28,777 6/1/2011 7/1/2018 7.09
29,402 4/1/2011 7/1/2018 7.25
39,865 2/1/2011 7/1/2018 7.42
37,132 11/1/2010 7/1/2018 7.67
44,558 9/1/2010 7/1/2018 7.84
45,619 8/1/2010 7/1/2018 7.92
37,636 8/1/2010 7/1/2018 7.92
54,106 5/1/2010 7/1/2018 8.17
43,272 5/1/2010 7/1/2018 8.17
63,654 9/1/2009 7/1/2018 8.84
29,147 9/1/2009 7/1/2018 8.84
36,000 6/1/2009 7/1/2018 9.09
49,230 5/1/2009 7/1/2018 9.17
29,147 4/1/2009 7/1/2018 9.25
42,436 2/1/2009 7/1/2018 9.42
33,418 10/1/2008 7/1/2018 9.75
43,497 8/1/2008 7/1/2018 9.92
55,001 7/1/2008 7/1/2018 10.01
37,918 4/1/2008 7/1/2018 10.25
42,436 4/1/2008 7/1/2018 10.25
58,198 3/1/2008 7/1/2018 10.34
45,449 10/1/2007 7/1/2018 10.76
47,741 5/1/2007 7/1/2018 11.18
31,827 4/1/2007 7/1/2018 11.26
37,547 11/1/2006 7/1/2018 11.67
47,512 10/1/2006 7/1/2018 11.76
50,081 8/1/2006 7/1/2018 11.92
58,774 6/1/2006 7/1/2018 12.09
44,786 5/1/2006 7/1/2018 12.18
37,917 2/1/2006 7/1/2018 12.42
86,199 6/1/2005 7/1/2018 13.09
36,000 6/1/2005 7/1/2018 13.09
37,918 4/1/2005 7/1/2018 13.26
43,497 12/1/2004 7/1/2018 13.59
28,012 10/1/2004 7/1/2018 13.76
32,288 10/1/2004 7/1/2018 13.76
89,116 9/1/2004 7/1/2018 13.84
32,967 8/1/2004 7/1/2018 13.92
28,484 8/1/2004 7/1/2018 13.92
33,418 7/1/2004 7/1/2018 14.01
54,436 8/1/2003 7/1/2018 14.93
53,395 8/1/2003 7/1/2018 14.93
56,228 7/1/2003 7/1/2018 15.01
44,558 5/1/2003 7/1/2018 15.18
75,705 4/1/2003 7/1/2018 15.26
47,741 12/1/2002 7/1/2018 15.59
53,918 1/1/2002 7/1/2018 16.51
30,913 7/1/2001 7/1/2018 17.01
47,512 6/1/2001 7/1/2018 17.09
80,628 9/1/2000 7/1/2018 17.84
43,600 2/1/2000 7/1/2018 18.42
55,236 1/1/2000 7/1/2018 18.51
57,342 8/1/1999 7/1/2018 18.93
37,132 8/1/1999 7/1/2018 18.93
29,044 8/1/1998 7/1/2018 19.93
80,737 6/1/1998 7/1/2018 20.10
29,175 1/1/1998 7/1/2018 20.51
34,479 6/1/1997 7/1/2018 21.10
35,090 3/1/1996 7/1/2018 22.35
55,056 1/1/1996 7/1/2018 22.51
33,203 7/1/1995 7/1/2018 23.02
53,045 8/1/1994 7/1/2018 23.93
43,150 9/1/1993 7/1/2018 24.85
27,053 7/1/1993 7/1/2018 25.02
34,018 12/1/1989 7/1/2018 28.60
58,655 7/1/1986 7/1/2018 32.02
60,415 4/1/1986 7/1/2018 32.27
34,847 11/1/1984 7/1/2018 33.68
60,415 8/1/1983 7/1/2018 34.94
48,695 9/1/1982 7/1/2018 35.85
55,497 7/1/1981 7/1/2018 37.02
Click to expand...


paste this code into module and saveAS workbook xlsm format.


-----
To create module follow below step
Insert VBA code to Excel Workbook

  • Open your workbook in Excel.
  • Press Alt + F11 to open Visual Basic Editor (VBE).
  • Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu




-----






Change sheet name in code if require and Run.

Code: 
Sub YTDSalary()
Dim oSalary As Currency, revised_Salary
Dim oYear As Long, i As Long


Worksheets("Sheet1").Activate
lstRecrd = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row   '''Change Sheet name here




For i = 2 To lstRecrd
oYear = Cells(i, "D").Value
oSalary = Cells(i, "A").Value
revised_Salary = oSalary


Select Case oYear
     Case 0.1 To 4.99
        revised_Salary = oSalary * 0.01
    
    Case 5 To 9.9
        revised_Salary = oSalary * 0.02
    Case 10 To 14.9
        oSalary = oSalary * 0.03
    Case Is > 14.9
        revised_Salary = oSalary * 0.04
    End Select
    Cells(i, "H").Value = revised_Salary + oSalary '''''output Column you can change here
Next i
End Sub
 
Upvote 0
MUKESHY12390 said:
paste this code into module and saveAS workbook xlsm format.


-----
To create module follow below step
Insert VBA code to Excel Workbook

  • Open your workbook in Excel.
  • Press Alt + F11 to open Visual Basic Editor (VBE).
  • Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu



-----






Change sheet name in code if require and Run.

Code: 
Sub YTDSalary()
Dim oSalary As Currency, revised_Salary
Dim oYear As Long, i As Long


Worksheets("Sheet1").Activate
lstRecrd = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row   '''Change Sheet name here




For i = 2 To lstRecrd
oYear = Cells(i, "D").Value
oSalary = Cells(i, "A").Value
revised_Salary = oSalary


Select Case oYear
     Case 0.1 To 4.99
        revised_Salary = oSalary * 0.01
    
    Case 5 To 9.9
        revised_Salary = oSalary * 0.02
    Case 10 To 14.9
        oSalary = oSalary * 0.03
    Case Is > 14.9
        revised_Salary = oSalary * 0.04
    End Select
    Cells(i, "H").Value = revised_Salary + oSalary '''''output Column you can change here
Next i
End Sub
Click to expand...

Thank you for your help. The problem is that I can't get it to work because I have some other fields in here that have names and departments that I can't share, but I think I got the it.
Thanks again for your help! Appreciate it.
 
Upvote 0
I am getting the following error: Subscript out of range. Is there a way I can send you the document privately?
Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Anniversary Date Question - Excel
Replies
1
Views
350
awoohaw
awoohaw
A
  • Question Question
Annual Bonus Prorated
Replies
1
Views
1K
t0ny84
T
R
  • Question Question
Interest Calculation
Replies
3
Views
486
SanjayGMusafir
SanjayGMusafir
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
374
Trixterz
T
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
568
mrshuck
M

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

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