if else formula for dynamic range

Hi,

i need help again for a formula to use in a dynamic range.

Emp Id Experience(yrs) Tenure
123 4.5 4-5 yrs
124 0.8 0-1 yrs
535 6 yrs > 5 yrs


Basically i have a dynamic range of emp Id and Experience, i need to write a formula to bring values like (0-1 yrs, 1-2 yrs, etc) in column Tenure.

for the below code (truncated all ifs and else to avoid clutter), error arises: object variable or with block variable not set

Private Sub MyMacro4()
         
      'Calculate Range of Tenure
      
      Dim ws As Worksheet
        Set ws = Worksheets("Separation")
              
           Dim exp As Range
           exp = ws.Range("E22:E" & Range("E22").End(xlDown).Row).Value
           
           Dim Tenre As Range
           Tenre = ws.Range("F22:E" & Range("E22").End(xlDown).Row).Value
                      
           For Each exp In ws.Range("E22:E" & Range("E22").End(xlDown).Row)
           If exp >5 Then Tenre = "> 5 yrs"
            
               Next exp
                       
      End Sub

Thanks in advance,
Anjale

You need to 'Set' your ranges like you did with your worksheet

Set exp = ws.Range("E22:E" & Range("E22").End(xlDown).Row)

Try this untested code

Private Sub MyMacro4()
Dim ws As Worksheet, exp As Range, r As Range, sResult As String

Set ws = Worksheets("Separation")
Set exp = ws.Range("E22:E" & Range("E22").End(xlDown).Row)
                
For Each r In exp.Cells
    If IsNumeric(r.Value) Then
        Select Case r.Value
            Case Is > 5
                sResult = "> 5 yrs"
            Case Is > 1
                sResult = "1-2 yrs"
            Case Is < 1
                sResult = "0-1 yr"
        End Select
        r.Offset(, 1).Value = sResult
    End If
Next r

End Sub

I don't know what you are trying to point out

I am resetting sResult to none for each loop in the below code to avoid false result

Private Sub MyMacro4()
Dim ws As Worksheet, exp As Range, r As Range, sResult As String

Set ws = Worksheets("Separation")
Set exp = ws.Range("E22:E" & Range("E22").End(xlDown).Row)
                
For Each r In exp.Cells
    If IsNumeric(r.Value) Then
        sResult = ""
        Select Case r.Value
            Case Is > 5
                sResult = "> 5 yrs"
            Case Is > 1
                sResult = "1-2 yrs"
            Case Is < 1
                sResult = "0-1 yr"
        End Select
        r.Offset(, 1).Value = sResult
    End If
Next r

End Sub