vba to filter, count and put result on different Sheet

Hi Experts, I have looked through the EE and there are quite a few examples of filter and count but,

I need to filter, then count the number of items in the filter and put the result onto Sheet1 in cell E6

i.e. on the active sheet when I filter column N with a date [that is input through an input box] lets say there are 8 items in the filter I need to then put 8 in sheet1 in cell E6

Hope someone can help.

Thanks
JagwarmanAsked:
Who is Participating?
 
gowflowCommented:
Here it is.

I fixed your sub Filterit and modified it to suit what you want.

Sub filterIt()
Dim W1Startdate3 As String, W1Startdate4 As String, TempDate As String
Dim WS As Worksheet
Dim Rng As Range, cCell As Range
Dim MaxRow As Long, I As Long

Set WS = ActiveSheet
WS.Range("1:1").AutoFilter


Do
    W1Startdate3 = Application.InputBox("Enter the Third Date (Wednesday) in DDMMYY format")
    If W1Startdate3 <> "False" Then
        TempDate = Left(W1Startdate3, 2) & "/" & Mid(W1Startdate3, 3, 2) & "/" & Right(W1Startdate3, 2)
    End If
Loop Until IsDate(TempDate) Or W1Startdate3 = "False"

Do
    W1Startdate4 = Application.InputBox("Enter the Fourth Date (Thurday) in DDMMYY format")
    If W1Startdate4 <> "False" Then
        TempDate = Left(W1Startdate4, 2) & "/" & Mid(W1Startdate4, 3, 2) & "/" & Right(W1Startdate4, 2)
    End If
Loop Until IsDate(TempDate) Or W1Startdate4 = "False"


If W1Startdate3 = "False" Or W1Startdate4 = "False" Then
    WS.AutoFilterMode = False
    Exit Sub
End If

WS.UsedRange.AutoFilter Field:=14, Criteria1:="=" & W1Startdate3, Operator:=xlOr, Criteria2:="=" & W1Startdate4
MaxRow = WS.Range("N" & WS.Rows.Count).End(xlUp).Row
Set Rng = WS.Range("N1:N" & MaxRow).SpecialCells(xlCellTypeVisible)

If MaxRow > 1 Then
    For Each cCell In Rng
        I = I + 1
    Next cCell
Else
    I = 1
End If

Sheets("Sheet1").Range("E6") = I - 1
End Sub

Open in new window


But I think you will not get what you want as your date are not correctly formatted. They are not recognized as date in Excel DDMMYY but anyhow see if this is what you want. Each time activate the macro Filterit and give your answers to first and second Inputbox and see results in sheet1. If you press on the first or second Inputbox Cancel then you will get also no filtering as you need to have 2 values there.

Let me know.

See attached file as well.
gowflow
filter-and-count-V01.xlsm
0
 
gowflowCommented:

i.e. on the active sheet when I filter column N with a date [that is input through an input box] lets say there are 8 items in the filter I need to then put 8 in sheet1 in cell E6

How do you filter them thru VBA ?
Can you post the code or a workbook ?

gowflow
0
 
scsymeCommented:
If you are using an Excel table then you can use the SUBTOTAL function. For example if you have a table called MyTable that spans range A1:P100 and you filter column N, called MyDate, for a specific date then the following function will give the number of rows that are now visible in the table:
=SUBTOTAL(103,MyTable[MyDate])

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JagwarmanAuthor Commented:
File attached
filter-and-count.xlsm
0
 
scsymeCommented:
Put the following into cell E6 of sheet1 and I think you'll get the results that you are after:
=SUBTOTAL(103,Sheet2!N:N)-1

Open in new window


The -1 at the end is because the count will also include the header row. Note that if there is other 'noise' in the column outside the data range then you may not be able to use N:N
0
 
Rob HensonFinance AnalystCommented:
Have the input box put the date value into a cell and then you can use the COUNTIF function in E6:

=COUNTIF(Sheet2,DateCell)

The data doesn't have to be filtered for this to work.

Thanks
Rob H
0
 
FarWestCommented:
Also you can use COUNTIF excel function
. make the inputbox put the input value in a cell and use its value as the criteria
0
 
JagwarmanAuthor Commented:
gowflow. Brilliant thanks
0
 
JagwarmanAuthor Commented:
a great expert, always delivers
0
 
gowflowCommented:
Thank you and appreciate your comment. Pls feel free to advise if you need help for any other issue, will be glad to assist.
gowlfow
0
 
JagwarmanAuthor Commented:
I have posted a new question which I am sure you will be able to help me with titled "copy data from several sheets into a summary sheet"
0
 
JagwarmanAuthor Commented:
scsyme thanks for your input I am sure I will be able to use this in other files but I need VBA in this particular instance
0
 
gowflowCommented:
Can you post the link to the question here ?
gowflow
0
 
scsymeCommented:
I appreciate the acknowledgement. Glad you got what you needed :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.