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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Than you so much for your response. I will check the code and let you know
 
Upvote 0
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)"
 
Upvote 0
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
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,789
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top