Need to add a fourth .autofilter when formula sends an error (explanation below)

[Nlhicks]

I am using this right now and it works but, sometimes criteria 1 field 10=field11=field12 and then it will either return an error or will bring up only the first occurrence. For this occasion, I need to add a fourth .autofilter which you can see commented out below. If I leave it in, nothing gets returned because it is looking for all four values. The fourth criteria is only needed in rare occasions so I would not normally have someone enter a value into that cell unless they get an error with the three conditions they entered. That would be how they know that they need to supply the fourth condition. I am not sure how to write that so that it will work all the time with or without the fourth condition.


Sub FindRightCell()

With Worksheets("Sheet2").Range("A1")

.AutoFilter Field:=10, Criteria1:="*" & Worksheets("Line Update").Range("C5") & "*"
.AutoFilter Field:=11, Criteria1:="*" & Worksheets("Line Update").Range("C6") & "*"
.AutoFilter Field:=12, Criteria1:=Worksheets("Line Update").Range("C7")
'.AutoFilter field:=13, Criteria1:=Worksheets("Line Update").Range("F6")

End With


Worksheets("Line Update").Range("B11").Value = Worksheets("Sheet2").Range("B2:B685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("B12").Value = Worksheets("Sheet2").Range("C2:C685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("B13").Value = Worksheets("Sheet2").Range("D2:D685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("B14").Value = Worksheets("Sheet2").Range("E2:E685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("B15").Value = Worksheets("Sheet2").Range("F2:F685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("B16").Value = Worksheets("Sheet2").Range("G2:G685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("B17").Value = Worksheets("Sheet2").Range("H2:H685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("B18").Value = Worksheets("Sheet2").Range("I2:I685").SpecialCells(xlCellTypeVisible)

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:P" & LR).SpecialCells(xlCellTypeVisible).Select

'With ActiveSheet.Worksheet("Sheet2").Rows("2:3").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ColorIndex = 34
.TintAndShade = 0
.PatternTintAndShade = 0
End With

 

[Nlhicks]

Slight correction to above, I do not get an error when I do my autofiltering, excel actually pulls up all four rows but I only want it to pull up the right one via the fourth autofilter.

 

[kevin9999]

I would simply wrap the fourth filter inside an IF statement, something like:

If <certain condition is/isn't met> Then
    .AutoFilter field:=13, Criteria1:=Worksheets("Line Update").Range("F6")
End If

That way, the fourth filter only gets applied when that "rare occasion" happens.

 

[Nlhicks]

Kevin, I can try this, is there a way to autofilter again if there is more than one line showing after the filters are applied?

 

[kevin9999]

Kevin, I can try this, is there a way to autofilter again if there is more than one line showing after the filters are applied?

I'm sorry, but I don't understand what you're asking. Could you provide some before-and-after samples of data to explain what you mean?