Won't return last row on spreadsheet and I only want Call Item_Open to work if first condition If Application.CountA(y) = 1 is false

[Nlhicks]

Sub FindRightCell()

Dim LineUpdate As Worksheet, Sheet2 As Worksheet
Dim x As Long
Dim y As Range, z As Range
Dim c As Range
Dim resp As VbMsgBoxResult
Dim ans As Variant


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

Worksheets("Line Update").Range("C11").Value = Worksheets("Sheet2").Range("B:B").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C12").Value = Worksheets("Sheet2").Range("C:C").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C13").Value = Worksheets("Sheet2").Range("D:D").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C14").Value = Worksheets("Sheet2").Range("E:E").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C15").Value = Worksheets("Sheet2").Range("F:F").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C16").Value = Worksheets("Sheet2").Range("G:G").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C17").Value = Worksheets("Sheet2").Range("H:H").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C18").Value = Worksheets("Sheet2").Range("I:I").SpecialCells(xlCellTypeVisible).

GoTo NoTOBus
Exit For
ElseIf Application.CountA <> 1 Then
GoTo TOBus
TOBus:
If LineUpdate.Range("H6").Value <> "" Then .AutoFilter field:=13, Criteria1:=LineUpdate.Range("H6")
Call Item_Open

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)

Exit For
End If
Next
End With

NoTOBus:

End With
End Sub

 

[Ottsel]

Do you have some screenshots as well as the "Item_Open" macro?

 

[Nlhicks]

Sub Item_Open()

Dim sValue As String

sValue = Application.InputBox("Enter the TO: Bus Number here, Thank you.")
Worksheets("Line Update").Range("H6").Value = sValue


Debug.Print sValue


End Sub

 

[Nlhicks]

 

[Nlhicks]

Would anything else be helpful?

 

[Nlhicks]

Here is what I get back when I put in the bus number 659226 which lives in the last row of Sheet2 but for every other bus number and even every row with out a bus number I get the right data to populate but in this case, the filter happens like it should but my cells (C11:C18) do not populate the values from Sheet2 like the picture I sent above does.

 

[Nlhicks]

I think it is making me put the number in twice, once for the first condition and once for the second condition. I only want it to ask for the second condition. The first condition should process without needing a number input.

 

[Nlhicks]

Any suggestions

 

[Nlhicks]

The Application CountA returns 14 for each line code that appears in the spreadsheet so I cannot use that to make my conditional logic work. Is there another way to count how many rows of filtered data there are? When I put in Rowz = rnData.SpecialCells(xlCellTypeVisible).Rows.Count and then Debug.Print Rowz and there are 4 rows showing it is only printing a 1. I think I am telling it to only print a one based on the criteria but it should be printing a 4 right?




Sub FindRightCell()

Dim LineUpdate As Worksheet, Sheet2 As Worksheet
Dim x As Long
Dim y As Range, z As Range
Dim c As Range
Dim resp As VbMsgBoxResult
Dim ans As Variant
Dim rnData As Range


Set LineUpdate = Worksheets("Line Update")
Set Sheet2 = Worksheets("Sheet2")
Set rnData = Sheet2.UsedRange

With rnData

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")


Rowz = rnData.SpecialCells(xlCellTypeVisible).Rows.Count
Debug.Print Rowz