Simplify my code

[kamalm]

Hi, I use this code to insert values at Column J based on Column B. This code works perfectly fine but I think this is too long. Perhaps there are others ways to simplify my code.

 Dim LastRow As Long
    Dim z As Long
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    For z = 2 To LastRow
        If Range("B" & z).Value = "A-01" Then
            Range("J" & z).Value = "Min. Material Level"
        ElseIf Range("B" & z).Value = "A-02" Then
            Range("J" & z).Value = "Min. Material Level"
        ElseIf Range("B" & z).Value = "A-03" Then
            Range("J" & z).Value = "Min. Material Level"
        ElseIf Range("B" & z).Value = "A-04" Then
            Range("J" & z).Value = "Min. Material Level"
        ElseIf Range("B" & z).Value = "A-05" Then
            Range("J" & z).Value = "Min. Material Level"
        ElseIf Range("B" & z).Value = "A-06" Then
            Range("J" & z).Value = "Min. Material Level"
        ElseIf Range("B" & z).Value = "A-58" Then
            Range("J" & z).Value = "Min. Material Level"
        ElseIf Range("B" & z).Value = "A-59" Then
            Range("J" & z).Value = "Min. Material Level"
        
        ElseIf Range("B" & z).Value = "A-07" Then
            Range("J" & z).Value = "Material Flow"
        ElseIf Range("B" & z).Value = "A-08" Then
            Range("J" & z).Value = "Material Flow"
        ElseIf Range("B" & z).Value = "A-09" Then
            Range("J" & z).Value = "Material Flow"
        ElseIf Range("B" & z).Value = "A-10" Then
            Range("J" & z).Value = "Material Flow"
        ElseIf Range("B" & z).Value = "A-11" Then
            Range("J" & z).Value = "Material Flow"
        ElseIf Range("B" & z).Value = "A-12" Then
            Range("J" & z).Value = "Material Flow"
        ElseIf Range("B" & z).Value = "A-60" Then
            Range("J" & z).Value = "Material Flow"
        ElseIf Range("B" & z).Value = "A-61" Then
            Range("J" & z).Value = "Material Flow"
            
        ElseIf Range("B" & z).Value = "A-13" Then
            Range("J" & z).Value = "Doser Deviation"
         ElseIf Range("B" & z).Value = "A-14" Then
            Range("J" & z).Value = "Doser Deviation"
         ElseIf Range("B" & z).Value = "A-15" Then
            Range("J" & z).Value = "Doser Deviation"
         ElseIf Range("B" & z).Value = "A-16" Then
            Range("J" & z).Value = "Doser Deviation"
          ElseIf Range("B" & z).Value = "A-17" Then
            Range("J" & z).Value = "Doser Deviation"
         ElseIf Range("B" & z).Value = "A-18" Then
            Range("J" & z).Value = "Doser Deviation"
         ElseIf Range("B" & z).Value = "A-62" Then
            Range("J" & z).Value = "Doser Deviation"
         ElseIf Range("B" & z).Value = "A-63" Then
            Range("J" & z).Value = "Doser Deviation"
            
         ElseIf Range("B" & z).Value = "A-24" Then
            Range("J" & z).Value = "SAF.SW.Tripped Or No Comp. Air"
         
         End If
    Next z

 

[dmt32]

Hi,
untested but one way maybe

Option Base 1
Sub simplify_my_code()
    Dim LastRow As Long, z As Long
    Dim i As Integer
    Dim arr As Variant, m As Variant
    
    arr = Array(Array("A-01", "A-02", "A-03", "A-04", "A-05", "A-06", "A-58", "A-59"), _
                Array("A-07", "A-08", "A-09", "A-10", "A-11", "A-12", "A-60", "A-61"), _
                Array("A-13", "A-14", "A-15", "A-16", "A-17", "A-18", "A-62", "A-63"), _
                Array("A-24"))
                
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    
    For z = 2 To LastRow
        For i = 1 To 4
            m = Application.Match(Range("B" & z).Value, arr(i), 0)
            If Not IsError(m) Then Range("J" & z).Value = _
            Choose(i, "Min. Material Level", "Material Flow", _
                      "Doser Deviation", "SAF.SW.Tripped Or No Comp. Air"): Exit For
        Next i
    Next z
End Sub

Note Option Base 1 statement - This MUST sit at VERY TOP of your code page OUTSIDE any procedure.

Dave

 

[kamalm]

Option Base 1
Sub simplify_my_code()
    Dim LastRow As Long, z As Long
    Dim i As Integer
    Dim arr As Variant, m As Variant
    
    arr = Array(Array("A-01", "A-02", "A-03", "A-04", "A-05", "A-06", "A-58", "A-59"), _
                Array("A-07", "A-08", "A-09", "A-10", "A-11", "A-12", "A-60", "A-61"), _
                Array("A-13", "A-14", "A-15", "A-16", "A-17", "A-18", "A-62", "A-63"), _
                Array("A-24"))
                
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    
    For z = 2 To LastRow
        For i = 1 To 4
            m = Application.Match(Range("B" & z).Value, arr(i), 0)
            If Not IsError(m) Then Range("J" & z).Value = _
            Choose(i, "Min. Material Level", "Material Flow", _
                      "Doser Deviation", "SAF.SW.Tripped Or No Comp. Air"): Exit For
        Next i
    Next z
End Sub

Note Option Base 1 statement - This MUST sit at VERY TOP of your code page OUTSIDE any procedure.

Dave

hey thanks mate. your code perfectly working! God bless you