Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > If...Elseif...End If
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default If...Elseif...End If
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,337
Default If...Elseif...End If
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
SalesAid Software

"javab98" wrote in message
om...
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,337
Default If...Elseif...End If
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
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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
SalesAid Software

"javab98" wrote in message
om...
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




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
IF, ELSEIF STATEMENTS Derrick Excel Discussion (Misc queries) 5 June 19th 09 06:56 PM
Still having problems with If --Elseif chrisnsmith Excel Discussion (Misc queries) 13 February 9th 09 07:19 PM
Elseif? ibrokit Excel Worksheet Functions 5 November 25th 08 04:28 PM
if elseif flow23 Excel Discussion (Misc queries) 0 November 14th 05 02:09 PM
elseif formula macrodummy Excel Discussion (Misc queries) 1 October 7th 05 01:43 PM


All times are GMT +1. The time now is 12:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement