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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Try this, I'm only just learning how to code in VBA so be warned there may be some errors with it, make a copy of your file to test it in. It appears to work for me, let me know how you get on with it. It is a looping function, which assumes there are no empty cells down the sheet, and will exit as soon as it finds an empty cell.


VBA Code:
Sub Copy_ERRor()
Dim cL As Range
Dim Ws As Worksheet
    Set Ws = Worksheets("PIR Template")
    

For Each cL In Ws.Range("Z:Z")
    If cL.Value = ("Error") Then
        cL.Copy
        cL.Offset(, 1).PasteSpecial xlPasteValues
        ElseIf cL.Value = vbNullString Then
    Exit For
    End If
Next


End Sub
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Thank you for the suggestions
sure i will try this code
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Getting error in auto filter a particular value in VBA
I'm getting error while auto filter a value "Error" in column Z and i want to add a column after col Z only if i find value "Error" in col Z otherwise i dont want to add a column
I tried below code but it is not working. Could please help me on this ?

For Each cell In Range("Z2:Z" & LR)
If cell.Value = "Error" Then
Range("A1:AH1").AutoFilter Field:=26, Criteria1:="Error"
Columns("AA:AA").Select
Selection.Insert Shift:=xlToRight
Range("AA1").Value = "Comments"
End If
Exit For
Next
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,588

ADVERTISEMENT

Try:
VBA Code:
Sub InsertCol()
    If WorksheetFunction.CountIf(Range("Z:Z"), "Error") > 0 Then
        Columns("AA:AA").Insert Shift:=xlToRight
        Range("AA1").Value = "Comments"
    End If
End Sub
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Than you so much for your response. I will check the code and let you know
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes now working perfectly
But my doubt is if whenever i got "Error" in col Z only at that time i want to run this code otherwise no want to run this code
And my next question is already filter up "Error" criteria i want to run VLookup in col AA
So i'm stuck here also
Range("AA" & LR).Formula = "=VLOOKUP(RC[-2],ZLOE019!C1:C7,7,0)"
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Now i'm using like this
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"
    End If
    If WorksheetFunction.CountIf(Range("Z:Z"), "Error") > 0 Then
    Range("AA" & LR).Formula = "=VLOOKUP(RC[-2],ZLOE019!C1:C7,7,0)"
    End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,439
Office Version
  1. 365
Platform
  1. Windows
@Arunachaljois
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.

I have merged both threads.
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Sorry for that Fluff
Can please help me regarding code whatever i provided?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,176
Messages
5,527,251
Members
409,754
Latest member
ekTZ

This Week's Hot Topics

Top