Add the fourth filter only if there are more than one result from the first three filters

[Nlhicks]

This code works to find any value I need unless there are multiple lines with the same name and circuit number. At that point, it still filters but it filters all four lines. I need to add something to this that makes sure that only one line shows up on "Sheet2" and if not then use the fourth filter to make sure there is only one line.

Maybe like this If Worksheets("Sheet2").Range("A2:I685").SpecialCellsTypeVisible>1 Then
.AutoFilter field:=13, Criteria1:=Worksheets("Line Update").Range("H6")
End If

Does anyone have a suggestion? I need to not do any of my other code until I make sure there is only one line otherwise it messes us everything else.

Sub FindRightCell()

Dim LineUpdate As Worksheet
Set LineUpdate = Worksheets("Line Update")

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

If LineUpdate.Range("D5").Value <> "" Then .AutoFilter field:=10, Criteria1:="*" & Worksheets("Line Update").Range("D5") & "*"
If LineUpdate.Range("D6").Value <> "" Then .AutoFilter field:=11, Criteria1:="*" & Worksheets("Line Update").Range("D6") & "*"
If LineUpdate.Range("D7").Value <> "" Then .AutoFilter field:=12, Criteria1:=Worksheets("Line Update").Range("D7")
'If LineUpdate.Range("H6").Value <> "" Then .AutoFilter field:=13, Criteria1:=Worksheets("Line Update").Range("H6")
'End If

End With

Worksheets("Line Update").Range("C11").Value = Worksheets("Sheet2").Range("B2:B685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C12").Value = Worksheets("Sheet2").Range("C2:C685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C13").Value = Worksheets("Sheet2").Range("D2:D685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C14").Value = Worksheets("Sheet2").Range("E2:E685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C15").Value = Worksheets("Sheet2").Range("F2:F685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C16").Value = Worksheets("Sheet2").Range("G2:G685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C17").Value = Worksheets("Sheet2").Range("H2:H685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C18").Value = Worksheets("Sheet2").Range("I2:I685").SpecialCells(xlCellTypeVisible)

 

[Nlhicks]

I got this to work sort of: Not sure if I need to add any other delays in there to help them understand what to do. Advice?


Sub FindRightCell()

Dim LineUpdate As Worksheet, Sheet2 As Worksheet
Dim x As Long
Dim y As Range
Dim resp As VbMsgBoxResult
resp = MsgBox("Please enter the TO Bus Number, there are multiple matches, Thank You", vbRetryCancel)




Set LineUpdate = Worksheets("Line Update")
Set Sheet2 = Worksheets("Sheet2")

With Sheet2.Range("A1")

If LineUpdate.Range("D5").Value <> "" Then .AutoFilter field:=10, Criteria1:="*" & LineUpdate.Range("D5") & "*"
If LineUpdate.Range("D6").Value <> "" Then .AutoFilter field:=11, Criteria1:="*" & LineUpdate.Range("D6") & "*"
If LineUpdate.Range("D7").Value <> "" Then .AutoFilter field:=12, Criteria1:=LineUpdate.Range("D7")

With Sheet2.UsedRange
For Each y In .Rows
If Application.CountA > 1 Then
resp = vbRetry

If LineUpdate.Range("H6").Value <> "" Then .AutoFilter field:=13, Criteria1:=LineUpdate.Range("H6")
End If

Next

 

[steve the fish]

You could do this relatively simply with formulas.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Nlhicks]

Good morning Peter, do you have a suggestion for such a formula?

 

[Domenic]

Enter the following formula in C11 . . .

=LET(rng,Sheet2!A2:M685,filt,FILTER(rng,(INDEX(rng,0,10)=D5)*(INDEX(rng,0,11)=D6)*(INDEX(rng,0,12)=D7)),filt2,IF(ROWS(filt)>1,FILTER(filt,(INDEX(filt,0,13)=H6)),filt),IFERROR(IF(ROWS(filt2)>1,"More than one record found!",INDEX(filt2,{2;3;4;5;6;7;8;9})),"No record found!"))

Note that the formula returns the values from Column B through Column I from the row that meets the criteria, as you've outlined.

Hope this helps!