Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    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.

    Kevin
    Kevin

  2. #2
    5 Star Lounger
    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))

  3. #3
    3 Star Lounger
    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
    Kevin
    Kevin

  4. #4
    Plutonium Lounger
    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.

  5. #5
    5 Star Lounger
    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

  6. #6
    3 Star Lounger
    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

    Kevin
    Kevin

  7. #7
    5 Star Lounger
    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>


  8. #8
    3 Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •