Re: Count & Sum Consecutive (2x) appearance of Specific Numeric Values

Tech-Archive recommends: Fix windows errors by optimizing your registry

I have no idea what you want. I am sure my code could be adapted to meet
your conditions but you haven't successfully communicated those to me at
least.

--
Regards,
Tom Ogilvy


"Sam via OfficeKB.com" <u4102@uwe> wrote in message
news:6dab337b8ded4@xxxxxx
Hi Tom,

Thank you very much indeed for your reply and assistance. Your VBA Code is
in
essence what I'm looking for. Brilliant!

However, I think the phrasing of "my required Consecutive Count" was not
clear.

Tom Ogilvy wrote:
101: Rows 1,2,3 4,5 should be two

101: Rows 1,2,3 Should NOT be counted as I only want Numeric Values
counted
whose consecutive appearance(s) are qualified by no more than one
consecutive
appearance (per appearance) i.e; the original Numeric Value and then the
second instance.

129: Rows 1,2,3,4,5 should be one

129: Rows 1,2,3,4,5 Should NOT be counted - qualification reason as above.

Would be great if your VBA Code could reflect my required Consecutive
Count?

Cheers,
Sam

anyway, this worked for me:

Sub CountData()
Dim rng As Range, rng1 As Range
Dim cnt As Range, cell As Range
Dim cell1 As Range, cnt1 As Long
Set rng = Range("Data")
Set rng1 = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
For Each cell In rng1
Set rng2 = Nothing
For Each cell1 In rng
If cell1 = cell Then
If rng2 Is Nothing Then
Set rng2 = Cells(cell1.Row, 1)
Else
Set rng2 = Union(rng2, Cells(cell1.Row, 1))
End If
End If
Next
cnt1 = 0
For Each ar In rng2.Areas
If ar.Count > 1 Then
cnt1 = cnt1 + 1
End If
Next
cell.Offset(0, 1).Value = cnt1 "changed Cell Offset to (0, 2)" for
column "C"
Next
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200702/1



.

Relevant Pages

  • Re: Formulas containing hard coded values
    ... Dim R As Range, sdoit As String ... MsgBox "Cell contains hard codes" ... Dim Fml As String, LCtext As String ... For Each Rng In R.Precedents.Areas ...
    (microsoft.public.excel.programming)
  • RE: Offset(0, 1) and then Offset(1, 0)?
    ... Dim rngMyRange As Range ... Dim rngBlanks As Range ... Dim rng As Range ... How can I modify the code to get the cell ...
    (microsoft.public.excel.programming)
  • RE: Offset(0, 1) and then Offset(1, 0)?
    ... However, if there is another blank cell in this row, it overwrites the second ... Dim rngMyRange As Range ... Dim rngBlanks As Range ... Dim rng As Range ...
    (microsoft.public.excel.programming)
  • Re: Loop through Row Range, Excel 2000, 2003
    ... Dim cell As Range, cell1 As Range ... Then Exit Sub ...
    (microsoft.public.excel.programming)
  • RE: Offset(0, 1) and then Offset(1, 0)?
    ... However, if there is another blank cell in this row, it overwrites the second ... Dim rngMyRange As Range ... Dim rngBlanks As Range ... Dim rng As Range ...
    (microsoft.public.excel.programming)