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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,441
Office Version
  1. 365
Platform
  1. Windows
You are already receiving help, so no need for me.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Did my code not help? If so, what’s it doing/not doing for you?
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Yes it is working but i want to add column only if col Z has "Error" otherwise i don't want to add column
 

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Could you please clarify what you mean by “add column”
 

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, so what you’re wanting is it to insert a column between two other columns and then populate it with your “Errors” with the column caption “Comments”. Is this correct?
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Now i'm using code like this. Below code
I want to insert column if in col Z has "Error" then only i want to insert col in AA otherwise i dont want to add it
and then i want to use VLookup function if there is any "Error" in col Z
VBA Code:
If WorksheetFunction.CountIf(Range("Z:Z"), "Error") > 0 Then
        With Sheets("PIR Template")
                With .Range("A1:AH1")
                     .AutoFilter
                     .AutoFilter Field:=26, Criteria1:="Error"
                End With
        End With
        Columns("AA:AA").Insert Shift:=xlToRight
        Range("AA1").Value = "Comments"
 

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Ok, I’ll have a bit more of a look at it tomorrow, it’s nearly midnight here.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,181
Messages
5,527,278
Members
409,756
Latest member
punknwilly

This Week's Hot Topics

Top