Got error while adding column and filter

Arunachaljois

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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
123
Office Version
365
Platform
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
365
Platform
Windows
Thank you for the suggestions
sure i will try this code
 

Arunachaljois

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

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
365
Platform
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
365
Platform
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
365
Platform
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
44,604
Office Version
365
Platform
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
365
Platform
Windows
Sorry for that Fluff
Can please help me regarding code whatever i provided?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,105
Messages
5,509,255
Members
408,719
Latest member
padapinto

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top