Count same values in collumn

[Flash0220]

Hello,


I'm having the following little problem, and i'm hoping that someone at
this message board can help me solve it.

I have (in collumn A) a number of numerical values.
The collumn containing the values starts at cell A2 and ends at the row number
stored in the variable "EndRowDatabase".

A B
8043 1
8446 1
8501 2
8501 2
9289 1
9390 1
9465 2
9465 2

I also have a collumn (B) which lists how many times
the number (in collumn A) is in the database.

So if number 8501 appears 2 times in collumn A, the
number 2 has to appear in each cell next to the cell
containing the value 8501.

Is there an easy way to do this using VBA only ?
(I'm using the dutch version of Excel 2003.)

Best regards,


Flash0220

 

[WinteE]

Probeer deze code eens (Try this code)

Sub AantalWaarden()

Dim c As Variant
Dim d As Variant
Dim Teller As Long

  For Each c In Range("A2:" & EndRowDatabase)
    Teller = 0
    For Each d in Range("A2:" & EndRowDatabase)
      If c.Value = d.Value Then
        Teller = Teller + 1
      End If
    Next d
    c.OffSet(0,1).Value = Teller
  Next c

End Sub

Success

 

[Peter_SSs]

I don't know about Dutch version, but would this suffice?

With Range("B2:B" & EndRowDatabase)
    .Formula = "=COUNTIF(A$2:A$" & EndRowDatabase & ",A2)"
    .Value = .Value
End With

 

[WinteE]

Dutch version would be :

With Range("B2:B" & EndRowDatabase)
    .Formula = "=AANTAL.ALS(A$2:A$" & EndRowDatabase & ";A2)"
    .Value = .Value
End With

 

[Peter_SSs]

Dutch version would be :

With Range("B2:B" & EndRowDatabase)
    .Formula = "=AANTAL.ALS(A$2:A$" & EndRowDatabase & ";A2)"
    .Value = .Value
End With

Thanks. If there was 1,000 rows, that should be a lot simpler than looping 1,000 x 1,000 = 1,000,000 times.

 

[WinteE]

I agree with you Peter. Formulas in VBA is not my thing, but this is much faster than what I produced.

 

[Flash0220]

Hello,


Thanks a lot for this code.
It works perfectly.
(And very fast.)
Thanks !


Best regards,
Flash0220