Got error while adding column and filter

[Arunachaljois]

Got error while adding column and filter
In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is "Error" by filtering Z column and select only "Error" otherwise i don't want to add column
Could please help me out this

If cell.Range("Z2:Z" & LR) = "Error" Then
Columns("AA:AA").Select
Selection.Insert Shift:=xlToRight
Range("AA1").Value = "Comments"
End If
With Sheets("PIR Template")

.AutoFilterMode = False

With .Range("A1:AG1")

.AutoFilter

.AutoFilter Field:=26, Criteria1:="Error"
End With

End With

 

[mumps]

I don't understand why you are filtering your data. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Trueblue862]

Ok, so let me get this straight, you want to search column 'Z" for the word "Error" if it contains that word you want to insert a column to the right with the heading "Comments", do you then want to add the "Error" from column "Z" to column "AA"?

 

[Trueblue862]

Ok, This is what I have, if you only want to insert a column, only run the sub "Add_COlumn"and remove the sub "Copy_ERRor". If you want to then move the errors to column AA then run then copy the whole thing into VBA and run "Copy_ERRor".

Option Explicit
Dim cL As Range
Dim Ws As Worksheet

Sub Copy_ERRor()
    Add_COlumn
    Set Ws = Worksheets("PIR Template")
    

For Each cL In Ws.Range("Z:Z")
    If cL.Row <> 1 Then
    If cL.Value = ("Error") Or cL.Value = ("error") Or cL.Value = ("ERROR") Then
        cL.Copy
        cL.Offset(, 1).PasteSpecial xlPasteValues
        ElseIf cL.Value = vbNullString Then
    Exit For
    End If
    End If
Next
Application.CutCopyMode = False

End Sub
Sub Add_COlumn()


    Set Ws = Worksheets("PIR Template")
    

For Each cL In Ws.Range("Z:Z")
    If cL.Row <> 1 Then
    If cL.Value = ("Error") Or cL.Value = ("error") Or cL.Value = ("ERROR") Then
        cL.Offset(, 1).EntireColumn.Insert
        Range("AA1").Value = ("Comments")
    Exit For
    End If
    End If
    Next
End Sub

 

[Arunachaljois]

Thank you so much for your help
Now it is working fine for me
Thanks

 

[Trueblue862]

No problem.