Results 1 to 8 of 8
Thread: counting cells (office 2003)

20071005, 21:06 #1
 Join Date
 Jan 2001
 Location
 New England (Connecticut)
 Posts
 200
 Thanks
 1
 Thanked 0 Times in 0 Posts
counting cells (office 2003)
Hi all,
I am trying to loop through a column of numbers in which some are the same number and some are different numbers. (Example: 775,775,775,776,777). I need to determine how many times the number changes. So for the example, there is a column with 5 numbers. cells A2 through A6 contain the values. Since 775 occurs three times, I only want to count it once. So at the end of the loop I should get a value of 3. I can do the basic loop with out any problem but do not understand how to set up the criteria of whether or not to count it. Thanks for any help.
KevinKevin

20071005, 21:14 #2
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells (office 2003)
If you want to count the unique items in a list, you can use the following array formula (confirm formula with ctrl+shift+enter):
=SUM(1/COUNTIF(A2:A6,A2:A6))

20071005, 21:25 #3
 Join Date
 Jan 2001
 Location
 New England (Connecticut)
 Posts
 200
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: counting cells (office 2003)
Thank You,
Since I am trying to do perform some tasks in the on open event, is there a way to incorporate this in a VBA loop? This is a situation where there may be 6 cells in the column or there may be 300 cells in the column. I was hoping to use vba to determine the number of rows with data by using ...
...Sheets("qrySSTARDataforHardCopyReport").Select
Range("A2", "A1000").Select
'count rows for loop
myCount = Application.CountA(Selection)...
... and then use the myCount variable to determine how many different values were in a specific column within that original range.
Thank You
KevinKevin

20071005, 21:40 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: counting cells (office 2003)
You can use something like this:
Dim col As New Collection
Dim r As Long
Dim m As Long
On Error Resume Next
With Worksheets("qrySSTARDataforHardCopyReport")
m = .Cells(65536, 1).End(xlUp).Row
' Loop through column A
For r = 2 To m
' Don't count blanks
If Not .Cells(r, 1) = "" Then
col.Add .Cells(r, 1), "A" & .Cells(r, 1)
End If
Next r
End With
Debug.Print col.Count
Set col = Nothing
Note that the code doesn't select the worksheet or any cell.
The code uses the fact that the key of items in a collection must be unique. If the code tries to add an item that has been added before, the On Error Resume Next line suppresses the error message and skips to the next line.

20071005, 21:44 #5
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells (office 2003)
My previous post count the unique items in a list. The code counts the number of times the value changes in a range. Currently the results are displayed as a message box. The code assumes no empty cells are in the A column.
The array formula, for the following data results in 3, while the code reults in a value of 8.
<table border 1><td>775</td><td>775</td><td>775</td><td>776</td><td>777</td><td>775</td><td>775</td><td>775</td><td>776</td><td>777</td><td>775</td><td>775</td><td>775</td><td>776</td></table>
Sub countChanges()
Dim cCell As Range, cCount As Long, last As Variant
Dim current As Variant, rCount As Long, i As Long
rCount = Cells(65536, 1).End(xlUp).Row  2
cCount = 1
last = Cells(rCount, 1)
For i = rCount To 2 Step 1
current = Cells(i, 1)
If current = last Then
cCount = cCount
last = current
Else
cCount = cCount + 1
last = current
End If
Next
MsgBox (cCount)
End Sub

20071009, 22:05 #6
 Join Date
 Jan 2001
 Location
 New England (Connecticut)
 Posts
 200
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: counting cells (office 2003)
Hi, thanks for the reply, I latched onto your concept for counting but do not understand how your code points to column A. If I wanted to apply this to other columns, what parts of the require changing. I have played with the various lines of code trying to point to different columns but am not changing all that need to be changed because my results are all over the place.
Thank You
KevinKevin

20071009, 22:16 #7
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells (office 2003)
The A column is referenced using Cells(Row,Column) instead of using the Range("A1") method. I've highlighted all references to the A column. Change the 1s to the column number you want to reference. B=2, C=3, etc.
<pre>Sub countChanges()
Dim cCell As Range, cCount As Long, last As Variant
Dim current As Variant, rCount As Long, i As Long
<font color=red>rCount = Cells(65536, 1).End(xlUp).Row  2</font color=red>
cCount = 1
<font color=red>last =Cells(rCount, 1)</font color=red>
For i = rCount To 2 Step 1
<font color=red>current = Cells(i, 1)</font color=red>
If current = last Then
cCount = cCount
last = current
Else
cCount = cCount + 1
last = current
End If
Next
MsgBox (cCount)
End Sub
</pre>

20071009, 22:19 #8
 Join Date
 Jan 2001
 Location
 New England (Connecticut)
 Posts
 200
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: counting cells (office 2003)
Thank You very much
Kevin