RE: Sort and count or just count?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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
.

Relevant Pages

  • RE: If cell is not null, add value to adjecent cell
    ... The below macro checks for data in Column A and if found fills Column B with ... "Hello" and loops until it reaches a null cell. ... Dim lngRow ...
    (microsoft.public.excel.programming)
  • RE: Exce Formating
    ... can I get the hightlight to be the length of the entire row with data? ... From tools macro run ... Dim lngRow As Long ... Dim lngLastRow As Long ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Exce Formating
    ... can I get the hightlight to be the length of the entire row with data? ... From tools macro run ... Dim lngRow As Long ... Dim lngLastRow As Long ...
    (microsoft.public.excel.worksheet.functions)
  • RE: delete rows within a specific range macro
    ... Sub Macro() ... Dim lngLastRow, lngRow, varValue ...
    (microsoft.public.excel.programming)
  • RE: macro - ignore row
    ... I need the macro to do everything exactly like "my macro" but to ignore ... Sub CopyPaste() ... Dim lngRow As Long, lngNRow As Long, wb As Workbook ...
    (microsoft.public.excel.misc)