Excel VBA IF Else question

[tharun27]

I have some basic knowledge and came up with a if else statement for 1 condition.. but unsure of what to do with 2 conditions.

so, If Column A says Employee, then do below.

Sub Employee()
 
Dim Days As Integer
Dim result As String
 
Days = Range("B2:B11").Value
 
If Days >= 41 Then
    result = "41"
 
ElseIf Days >= 36 Then
    result = "36-40"
   
ElseIf Days >= 26 Then
    result = "26-35"
   
ElseIf Days >= 0 Then
    result = "0-25"
   
Else
    result = "Future Dates"
   
End If
Range("C2:C11").Value = result
 
End Sub

But now, I need another condition, where if Column A says Dept, do another set of code similar to above.

In Column B, I have # of days, and I am wanting to have the result appear in Column C in the row next to each one, I'm able to get the result if I set range to only B2 and C2, however, it causes a bug when I try to do the range of B2:B11, for example.

What I would like to is for the code to loop for all cells in column B that are not NULL. I'm unsure of the syntax to use for that. appreciate your help in advance. and thank you

 

[Roxxien]

First thing first, welcome on the forum.

After that, the loop is your best option and if a little modification that will help you do complete correctly with a "Dept" section.

I putted a fictive range that you will need to modify

Sub Employee()
 
Dim Days As Range
Dim result As String
 
For Each Days In Range("A2:A10000")
    If Not IsNull(Days) Then
        If Days = "Employee" Then
            If Days.Offset(0, 1) >= 41 Then
                result = "41"
            ElseIf Days.Offset(0, 1) >= 36 Then
                result = "36-40"
            ElseIf Days.Offset(0, 1) >= 26 Then
                result = "26-35"
            ElseIf Days.Offset(0, 1) >= 0 Then
                result = "0-25"
            Else
                result = "Future Dates"
            End If
            Days.Offset(0, 2) = result
        Else
            'put what you want for dept here
        End If
    End If
Next
 
End Sub

 

[Fluff]

Hia & welcome to the board
How about this

Sub Employee()
 
    Dim Days As Range
    Dim result As String

Application.ScreenUpdating = False
    For Each Days In Range("B2:B" & Range("B2").End(xlDown).Row)
        Select Case Days
            Case Is >= 41
                Days.Offset(, 1) = "41"
            Case 36 To 40
                Days.Offset(, 1) = "36-40"
            Case 26 To 35
                Days.Offset(, 1) = "26-35"
            Case 0 To 25
                Days.Offset(, 1) = "0-25"
            Case Else
                Days.Offset(, 1) = "Future Dates"
        End Select
    Next Days
Application.ScreenUpdating = True

End Sub

This assumes that there are no blanks in column B until the last value

 

[tharun27]

I'm trying to do this...

Dim Days As Range
Dim result As String
For Each Days In Range("A2:A10000")
    If Not IsNull(Days) Then
     If Days = "Employee" Then
              If Days.Offset(0, 1) >= 41 Then
                 result = "41"
            ElseIf Days.Offset(0, 1) >= 36 Then
                result = "36-40"
             ElseIf Days.Offset(0, 1) >= 26 Then
                 result = "26-35"
             ElseIf Days.Offset(0, 1) >= 0 Then
                 result = "0-25"
             Else
                 result = "Future Dates"
             End If
            'Use the below code to create a loop:
             Days.Offset(0, 2) = result
         Else
         
            If Days = "Dept" Then
              If Days.Offset(0, 1) >= 16 Then
                 result = "16"
            ElseIf Days.Offset(0, 1) >= 11 Then
                result = "11-15"
             ElseIf Days.Offset(0, 1) >= 6 Then
                 result = "6-10"
             ElseIf Days.Offset(0, 1) >= 0 Then
                 result = "0-5"
             Else
                 result = "Future Dates"
             End If
            'Use the below code to create a loop:
             Days.Offset(0, 2) = result
End Sub

but getting some errors..

 

[Roxxien]

Replace what you previously posted by this as I corrected some error and added the end of the loop.

Dim Days As Range
Dim result As String
For Each Days In Range("A2:A10000")
    If Not IsNull(Days) Then
        If Days = "Employee" Then
            If Days.Offset(0, 1) >= 41 Then
                 result = "41"
            ElseIf Days.Offset(0, 1) >= 36 Then
                result = "36-40"
            ElseIf Days.Offset(0, 1) >= 26 Then
                 result = "26-35"
            ElseIf Days.Offset(0, 1) >= 0 Then
                 result = "0-25"
            Else
                 result = "Future Dates"
            End If
            'Use the below code to create a loop:
            Days.Offset(0, 2) = result
         ElseIf Days = "Dept" Then
            If Days.Offset(0, 1) >= 16 Then
                 result = "16"
            ElseIf Days.Offset(0, 1) >= 11 Then
                result = "11-15"
            ElseIf Days.Offset(0, 1) >= 6 Then
                 result = "6-10"
            ElseIf Days.Offset(0, 1) >= 0 Then
                 result = "0-5"
            Else
                 result = "Future Dates"
            End If
            'Use the below code to create a loop:
            Days.Offset(0, 2) = result
        End If
    End If
Next

If you still get error, tell us that it say in the message box and for which line.

 

[tharun27]

thank you, it worked. thanks so much

Replace what you previously posted by this as I corrected some error and added the end of the loop.

Dim Days As Range
Dim result As String
For Each Days In Range("A2:A10000")
    If Not IsNull(Days) Then
        If Days = "Employee" Then
            If Days.Offset(0, 1) >= 41 Then
                 result = "41"
            ElseIf Days.Offset(0, 1) >= 36 Then
                result = "36-40"
            ElseIf Days.Offset(0, 1) >= 26 Then
                 result = "26-35"
            ElseIf Days.Offset(0, 1) >= 0 Then
                 result = "0-25"
            Else
                 result = "Future Dates"
            End If
            'Use the below code to create a loop:
            Days.Offset(0, 2) = result
         ElseIf Days = "Dept" Then
            If Days.Offset(0, 1) >= 16 Then
                 result = "16"
            ElseIf Days.Offset(0, 1) >= 11 Then
                result = "11-15"
            ElseIf Days.Offset(0, 1) >= 6 Then
                 result = "6-10"
            ElseIf Days.Offset(0, 1) >= 0 Then
                 result = "0-5"
            Else
                 result = "Future Dates"
            End If
            'Use the below code to create a loop:
            Days.Offset(0, 2) = result
        End If
    End If
Next

If you still get error, tell us that it say in the message box and for which line.

 

[Roxxien]

This wasn't a big issue. You mainly forgot to put some "End IF" and "Next" at the end which would create a large issue.