If...Elseif...End If

[javab98]

I have two questions:

First of all, I have created a spreadsheet that tracks an employees
time, leave balances. On a Summary worksheet, I have all the
different pay periods that occur throughout the year. Depending on
the number of hours that an employee works in a given pay period will
determine how much annual leave they accrue. Also, depending on how
many years an employee has with the company, will determine what leave
category they fall into. For example if an employee has been with the
company for seven years they would be in leave category six. If that
employee only worked 40 hours in a two week pay period then they only
accrue three hours of annual leave. I have created an
If...Elseif...End If statement that works perfectly for leave category
four and six but will not work for eight. Below is the code:

B5 is the cell that the appropriate leave category is entered into
C48 is the total number of hours worked for that pay period
C53 is where the number of annual leave hours earned is entered

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As
Range)

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

If ws.Range("B5").Text = "4" Then
If ws.Range("C48").Text >= "0:00" And ws.Range("C48").Text <=
"19:00" Then
ws.Range("C53").Value = "0:00"
ElseIf ws.Range("C48").Text >= "20:00" And
ws.Range("C48").Text <= "39:00" Then
ws.Range("C53").Value = "1:00"
ElseIf ws.Range("C48").Text >= "40:00" And
ws.Range("C48").Text <= "59:00" Then
ws.Range("C53").Value = "2:00"
ElseIf ws.Range("C48").Text >= "60:00" And
ws.Range("C48").Text <= "79:00" Then
ws.Range("C53").Value = "3:00"
ElseIf ws.Range("C48").Text >= "80:00" Then
ws.Range("C53").Value = "4:00"
End If
ElseIf ws.Range("B5").Text = "6" Then
If ws.Range("C48").Text >= "0:00" And ws.Range("C48").Text <=
"12:00" Then
ws.Range("C53").Value = "0:00"
ElseIf ws.Range("C48").Text >= "13:00" And
ws.Range("C48").Text <= "25:00" Then
ws.Range("C53").Value = "1:00"
ElseIf ws.Range("C48").Text >= "26:00" And
ws.Range("C48").Text <= "38:00" Then
ws.Range("C53").Value = "2:00"
ElseIf ws.Range("C48").Text >= "39:00" And
ws.Range("C48").Text <= "51:00" Then
ws.Range("C53").Value = "3:00"
ElseIf ws.Range("C48").Text >= "52:00" And
ws.Range("C48").Text <= "64:00" Then
ws.Range("C53").Value = "4:00"
ElseIf ws.Range("C48").Text >= "65:00" And
ws.Range("C48").Text <= "77:00" Then
ws.Range("C53").Value = "5:00"
ElseIf ws.Range("C48").Text >= "78:00" Then
ws.Range("C53").Value = "6:00"
End If
ElseIf ws.Range("B5").Text = "8" Then
If ws.Range("C48").Text >= "0:00" And ws.Range("C48").Text <=
"9:00" Then
ws.Range("C53").Value = "0:00"
ElseIf ws.Range("C48").Text >= "10:00" And
ws.Range("C48").Text <= "19:00" Then
ws.Range("C53").Value = "1:00"
ElseIf ws.Range("C48").Text >= "20:00" And
ws.Range("C48").Text <= "29:00" Then
ws.Range("C53").Value = "2:00"
ElseIf ws.Range("C48").Text >= "30:00" And
ws.Range("C48").Text <= "39:00" Then
ws.Range("C53").Value = "3:00"
ElseIf ws.Range("C48").Text >= "40:00" And
ws.Range("C48").Text <= "49:00" Then
ws.Range("C53").Value = "4:00"
ElseIf ws.Range("C48").Text >= "50:00" And
ws.Range("C48").Text <= "59:00" Then
ws.Range("C53").Value = "5:00"
ElseIf ws.Range("C48").Text >= "60:00" And
ws.Range("C48").Text <= "69:00" Then
ws.Range("C53").Value = "6:00"
ElseIf ws.Range("C48").Text >= "70:00" And
ws.Range("C48").Text <= "79:00" Then
ws.Range("C53").Value = "7:00"
ElseIf ws.Range("C48").Text >= "80:00" Then
ws.Range("C53").Value = "8:00"
End If
End If

Set ws = Nothing

End Sub

First question: Any idea why it would not work for leave category 8?

I can not for the life of me figure out why it will not work. If I
enter leave category 8, it goes to the Elseif statement and begins to
work through the statement but it always thinks that the total number
of hours is between 0 and 9 and enters 0 for amount of leave earned.

Second question: I need this exact process to be run on 25 other pay
periods on this one worksheet ie. instead of evaluating C48 and
entering a result in C53, the next one would evalute c49 and enter the
result in C54, so on and so forth. Any idea on how I can do this?

Sorry for being long winded, but I've been battling this for about a
month now and can't come up with an answer.

Tina

 

[Don Guillett]

Why don't you use a simple lookup table. Have a look at HELP index for
LOOKUP, especially vLookup. Then, create a table and
=vlookup(c48,mytable,2,0). You could make another column for the c49

OR,
to use in a macro within the worksheet_change event, I would suggest using a
SELECTCASE vs if-then-else, Then you could incorporate this into a for/next
macro to to all of the c48,c49,c50, etc.

 

[Don Guillett]

Sample select case

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target
Case 1 To 19: x = 1
Case 20 To 39: x = 2
Case Else: x = "wrong"
'etc
'etc
End Select
MsgBox x
End Sub