Got error while adding column and filter

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,588
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).
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 24, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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".

VBA Code:
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

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Thank you so much for your help
Now it is working fine for me
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,109,179
Messages
5,527,270
Members
409,756
Latest member
punknwilly

This Week's Hot Topics

Top