PDA

View Full Version : New to VBA, trouble with an If Else Then statement



talrico
07-27-2013, 10:24 PM
I'm having some difficulty with a basic function in VBA. I'm attempting to place a person's name in a target cell if other specified cells contain certain strings of text. If they do not contain that string then it would not copy the person's name and the cell would be left blank. I'm getting an "Else without If" error even though all of the Elses have Ifs and Ifs have Thens and it highlights the text I've highlighted in red here when it tells me.


Sub DayShift()
Dim shift As String, day As String
'Sharon Day
shift = Range("C8").Value
If shift = "8a-4:30p" Then day = "Sharon"
ElseIf shift = "8a-6:30p" Then day = "Sharon"
ElseIf shift = "8a-8:30p" Then day = "Sharon"
ElseIf shift = "8a-12:30a" Then day = "Sharon"
End If
Range("AE8").Value = day
End Sub

patel
07-27-2013, 10:56 PM
2 ways


If shift = "8a-4:30p" Then day = "Sharon"
If shift = "8a-6:30p" Then day = "Sharon"
If shift = "8a-8:30p" Then day = "Sharon"
If shift = "8a-12:30a" Then day = "Sharon"



If shift = "8a-4:30p" Then
day = "Sharon"
ElseIf shift = "8a-6:30p" Then
day = "Sharon"
ElseIf shift = "8a-8:30p" Then
day = "Sharon"
ElseIf shift = "8a-12:30a" Then
day = "Sharon"
End If

talrico
07-27-2013, 11:44 PM
Okay, so it did work but now I'm trying to string together the same operation for the next cell and the one below that, like this:


Sub Dayshift()
Dim shift As String, day As String
'Sendja Day
shift = Range("C9").Value
If shift = "8a-4:30p" Then day = "Sendja"
If shift = "8a-6:30p" Then day = "Sendja"
If shift = "8a-8:30p" Then day = "Sendja"
If shift = "8a-12:30a" Then day = "Sendja"
Range("AE9").Value = day
'Maria Day
shift = Range("C10").Value
If shift = "8a-4:30p" Then day = "Maria"
If shift = "8a-6:30p" Then day = "Maria"
If shift = "8a-8:30p" Then day = "Maria"
If shift = "8a-12:30a" Then day = "Maria"
Range("AE10").Value = day
'Kathryn Day
shift = Range("C11").Value
If shift = "8a-4:30p" Then day = "Kathryn"
If shift = "8a-6:30p" Then day = "Kathryn"
If shift = "8a-8:30p" Then day = "Kathryn"
If shift = "8a-12:30a" Then day = "Kathryn"
Range("AE11").Value = day
'Carol Day
shift = Range("C12").Value
If shift = "8a-4:30p" Then day = "Carol"
If shift = "8a-6:30p" Then day = "Carol"
If shift = "8a-8:30p" Then day = "Carol"
If shift = "8a-12:30a" Then day = "Carol"
Range("AE12").Value = day
'Amy Day
shift = Range("C13").Value
If shift = "8a-4:30p" Then day = "Amy"
If shift = "8a-6:30p" Then day = "Amy"
If shift = "8a-8:30p" Then day = "Amy"
If shift = "8a-12:30a" Then day = "Amy"
Range("AE13").Value = day

End Sub


The trouble is that if the conditions aren't met for, say Carol, but are met for the person above her then that name is just copied into A12, the cell I'm trying to assign exclusively to Carol. Is there some linking command I should use? Or is there a way to create a recurssive command which would do the same thing? Maybe if I create a column next to the shifts that I could refer to with the employee's name?

Teeroy
07-28-2013, 01:13 AM
For multiple conditions of the one test Select Case would be a better way to code. e.g


shift = Range("C9").Value
Select Case shift
Case "8a-4:30p", "8a-6:30p", "8a-8:30p"
sDay = "Sendja"
End Select

Day is a very bad name for a variable as it is the name of a VBA function.

Another way of achieving your goal is:


Sub Dayshift2()
Dim rShift As Range
vCells = Array("C9", "C10", "C11", "C12", "C13")
vNames = Array("Sendja", "Maria", "Kathryn", "Carol", "Amy")
For i = LBound(vCells) To UBound(vCells)
Set rShift = Range(vCells(i))
Select Case rShift
Case "8a-4:30p", "8a-6:30p", "8a-8:30p"
Range("AE" & rShift.Row).Value = vNames(i)
End Select
Next i
End Sub

SamT
07-28-2013, 02:37 AM
Talrico,

I think that you are not telling us all you know. It looks like you are filling positions by shift, which implies that there are more than one shifts.
Any answer that we give you for one shift might not be compatible when you try to modify it to include more.

For example, here's yet another way to answer the limited information

Sub Dayshift()
Dim shift As String, day As String, Cel As Range
For Each Cel In Range("C9:C13")
Select Case Cel.Row
Case 9
day = "Sendja"
evening = "Bob"
midnight = "Tom"
Case 10: day = "Maria"
Case 11: day = "Kathryn"
Case 12: day = "Carol"
Case 13: day = "Amy"
End Select

If Left(Cel, 2) = "8a" Then Range("AE" & Cel.Row) = day
If Left(Cel, 2) = "4p" Then Range("AE" & Cel.Row) = evening
If Left(Cel, 3) = "12a" Then Range("AE" & Cel.Row) = midnight

Next Cel
End Sub

Depending on how complex your scheduling is, you might want to set up a matrix sheet. some suggestions for columns are:


First column is a list of employees.
Next column for total hours worked in last 16-24 hrs, your choice
Next for hours worked in last 40hrs - week- month, your choice of any period.
Skip a column
Next few columns are positions needing staffed, with tick marks for each employee qualified for that position.
Skip a column
Next 7 columns for each day of the week, with ticks for each employee's availability
Skip a column
Next 48 columns by 1/2 hour with x's for each employees hourly availability.
For true multicultural labor, columns for special holidays with check marks for each employee.