Sort and count or just count?

[Ron]

A1 ln, fn - Nothing else in 1st row
next row, or next 2 or 3 rows have data with either 7801 or 7802 in col f.
After data comes another ln, fn with nothing in row. So basically,
sscrolling down column f sepearates each person with a bloank line.

I did a loop that counted the 7801's and 7802's for each person, up to 10
rows of data. It puts the counts in of 7801 in col B of the name row and
7802's in col c. Worked like a champ......until I found under one name both a
7801 and a 7802.

Well, I don't want to put 10 loops under each 10 lops, etc...so...I was
wondring, should I sort the rows putting 7801 first then loop -de-loop, or is
there a way to count as I scroll 7801's and 7802's on the first pass? (Of
course there is, that's why I came to you guys....)

Data look like this
A B C D
E F
Doe, John 1 1
5/7/2009 1:31:20PM OUT 8 :00 7801
5/7/2009 1:31:20PM OUT 8 :00 7802
Smith, Jane 1 1
5/7/2009 1:31:20PM OUT 8 :00 7802
5/7/2009 1:31:20PM OUT 8 :00 7801
Brown, Suzie 1
5/7/2009 9:09:15AM OUT 13 :26 7802
Black, Dave
Lowe, Stacy 3
5/7/2009 10:29:05AM OUT 13 :48 7802
5/7/2009 3:01:58PM OUT 13 :33 7802
5/7/2009 5:25:24PM OUT 13 :25 7802
etc, etc

I out the counts in column B and C.

Now, how can I go about this....without my normal 60 lines of code??

Thanks,

Ron

 

[Jacob Skaria]

Hi Ron

Without much loops, the below macro which will count all 7801 and 7802 for
all groups. I assume 7801 and 7802 are in number format. If text change the
numbers mentioned within the countif to "7801" and "7802". Try and feedback

Sub Macro()
Dim lngRow As Long
Dim lngTemp As Long
Dim lngLastRow As Long

lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLastRow
If Left(Range("A" & lngRow), 1) Like "[A-Z]" Then
If lngTemp <> 0 And lngTemp <> lngRow - 1 Then
Range("B" & lngTemp) = WorksheetFunction.CountIf _
(Range("F" & lngTemp + 1 & ":F" & lngRow - 1), 7801)
Range("C" & lngTemp) = WorksheetFunction.CountIf _
(Range("F" & lngTemp + 1 & ":F" & lngRow - 1), 7802)
End If
lngTemp = lngRow
End If
Next
Range("B" & lngTemp) = WorksheetFunction.CountIf _
(Range("F" & lngTemp + 1 & ":F" & lngRow - 1), 7801)
Range("C" & lngTemp) = WorksheetFunction.CountIf _
(Range("F" & lngTemp + 1 & ":F" & lngRow - 1), 7802)
End Sub



If this post helps click Yes

 

[Jacob Skaria]

Ron

If you dont have any blank rows (completly blank) in between..the below will
do..

Sub Macro()
Dim lngRow As Long, lngTemp As Long, lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLastRow + 1
If Not IsNumeric(Left(Range("A" & lngRow), 1)) Then
If lngTemp <> 0 Then
Range("B" & lngTemp) = WorksheetFunction.CountIf _
(Range("F" & lngTemp + 1 & ":F" & lngRow - 1), 7801)
Range("C" & lngTemp) = WorksheetFunction.CountIf _
(Range("F" & lngTemp + 1 & ":F" & lngRow - 1), 7802)
End If
lngTemp = lngRow
End If
Next
End Sub

If this post helps click Yes

 

[keiji kounoike]

I presume that data start at A1 and data in column A is either name or
date and no blank cell between data.

Sub Counttest()
Dim Bcell As Range, Ecell As Range
Dim n As Long, m As Long
Dim vIN As Boolean

Set Bcell = Range("A1")
Set Ecell = Bcell.Cells(2, 1)
Do While (Bcell.Value <> "")
If Not IsDate(Bcell) Then vIN = True
Do While (Not IsDate(Ecell))
Set Bcell = Ecell
Set Ecell = Bcell.Cells(2, 1)
If Bcell.Value = "" Then
vIN = False
Exit Do
End If
Loop
Do While (IsDate(Ecell))
Set Ecell = Ecell.Cells(2, 1)
Loop
If Not vIN Then Exit Do
Set Ecell = Ecell.Cells(0, 1)
n = Bcell.Row
m = Ecell.Row
Cells(n, "C").Formula = "=countif(" & _
Range(Cells(n + 1, "F"), Cells(m, "F")).Address & ",7801)"
Cells(n, "D").Formula = "=countif(" & _
Range(Cells(n + 1, "F"), Cells(m, "F")).Address & ",7802)"
Set Bcell = Ecell.Cells(2, 1)
Set Ecell = Bcell.Cells(2, 1)
vIN = False
Loop
End Sub

Keiji

 

[Ron]

That worked perfectly....the only difference, in results, between yours and
keiji's was yours counted put zero's on every line, even when the name had no
data below it. They both worked and saved me a buch of lines of code and
frustration.

Thanks,

Ron

 

[Ron]

You presumed correctly and it worked perfectly. Both solutions worked and
gave me 2 different code sets to study and modify for use down the line.

Thanks,

Ron

 

[keiji kounoike]

I seemed like to put the result into wrong cells.
the code below

Cells(n, "C").Formula = "=countif(" & _
Range(Cells(n + 1, "F"), Cells(m, "F")).Address & ",7801)"
Cells(n, "D").Formula = "=countif(" & _
Range(Cells(n + 1, "F"), Cells(m, "F")).Address & ",7802)"

should be

Cells(n, "B").Formula = "=countif(" & _
Range(Cells(n + 1, "F"), Cells(m, "F")).Address & ",7801)"
Cells(n, "C").Formula = "=countif(" & _
Range(Cells(n + 1, "F"), Cells(m, "F")).Address & ",7802)"

Keiji