Why is this failing?

edtude

New Member
Joined
Mar 13, 2013
Messages
21
I am baffled by this, I am an admitted novice but have been able to cobble some good code together and am admittedly getting into stuff over my head. But this code is doing what I ask and still failing at the end....
Code:
Sub NewCountingMethod()        Sheets("Data").Select     Range("A1").AutoFilter Field:=1, Criteria1:="=*Clear*", Operator:=xlOr, Criteria2:="=*Major*"     Range("A1").AutoFilter Field:=4, Criteria1:="Receiver"     Range("A1").AutoFilter Field:=5, Criteria1:="<>*NO RFA FROM SITE*"                     Range("A1").AutoFilter Field:=3, Criteria1:="Site 2, Great Falls Ch13"         For Each Cell In Range("E:E").CurrentRegion.SpecialCells(xlCellTypeVisible)             If Cell.Value = "MAJOR FAILED, Rx Illegal Carrier" Then                 ActiveCell.Offset(0, -3).Activate                 'Resize(1, 0).Copy                     'Sheets("Great_P25").Range ("N49")                                  End If         Next Cell     'Range("A1").AutoFilter      End Sub
Ay help is appreciated
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
May we know what you want your code to do?
 

edtude

New Member
Joined
Mar 13, 2013
Messages
21
Sure, I have several thousand lines of data that I am using Autofilter to narrow the scope of my search. Even with Autofilter I still will have lines that I can not use for my analysis. So I am searching column E for a particular string of text. Once I find this text I want to copy the time of the failure which is the line that I am searching for and the following row which has the recovery. Then will paste them on a second sheet in the workbook and manipulate the data there. So far I can get the filtering to work, I can find the string of text that I am looking for and then I can get to the first cell containing the time of the failure. That is where the Debug occurs and I can’t for the life of me figure it out. Longwinded I know but you asked!

May we know what you want your code to do?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Maybe

Code:
Sub NewCountingMethod()
    Dim cell As Range
    
    With Worksheets("Data").Range("A1").CurrentRegion
        .Worksheet.Select
        .AutoFilter Field:=1, Criteria1:="=*Clear*", Operator:=xlOr, Criteria2:="=*Major*"
        .AutoFilter Field:=4, Criteria1:="Receiver"
        .AutoFilter Field:=5, Criteria1:="<>*NO RFA FROM SITE*"
        .AutoFilter Field:=3, Criteria1:="Site 2, Great Falls Ch13"

        For Each cell In .Range("E:E").SpecialCells(xlCellTypeVisible)
            If cell.Value = "MAJOR FAILED, Rx Illegal Carrier" Then
                cell.Offset(, -3).Copy Worksheets("Great_P25").Range("N49")
            End If
        Next cell
    End With
End Sub
Remember that string comparisons in VBA are case sensitive by default.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,157
Latest member
MrBJBones

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
Top