Excel VBA - count number of different parameters in table

Question

I have some problems with my excel VBA code, it does not work and yes, I do not know why...

Data table

I want to add each Record number once in a collection. My code looks like this:

For i = 1 To lo.ListRows.Count
    Count = 1
    Do While recordList.Count >= Count
        recordFound = False
        If lo.ListColumns("Record").DataBodyRange.Rows(i) = recordList(Count) Then
            recordFound = True
        End If
        If recordFound = False Then
            recordList.Add (lo.ListColumns("Record").DataBodyRange.Rows(i))
        End If
        Count = Count + 1
    Loop
Next

What it does now, it returns empty collection...

Whould be great if you could help me guys!

Assuming you start with an empty collection, this part will never be true: Do While recordList.Count >= Count since 0 < 1. — Rory
– Rory, Commented Sep 10, 2014 at 12:25
Yeap, that is true. I also found that I have error in my structure. If statement for recordFound have to be in for loop... — Advice Me
– Advice Me, Commented Sep 10, 2014 at 12:34

Ron Rosenfeld · Accepted Answer · 2014-09-11 00:03:21Z

2

There is no real need to test the Collection to see if the item exists if you give it a key. You can code something like:

On Error Resume Next
For I = 1 To lo.ListRows.Count
    With lo.ListColumns("Record").DataBodyRange.Rows(I)
        RecordList.Add Item:=.Value, Key:=CStr(.Value)
    End With
Next I
On Error GoTo 0

Adding an item with the same key will cause the operation to be rejected. If you are concerned about other errors than the duplicate key error, you can always check the error number in the inline code and branch depending on the results.

answered Sep 11, 2014 at 0:03

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave · Accepted Answer · 2014-09-10 12:23:17Z

I haven't been able to test this with the reference to lo but it works with a reference to a range

Dim objDictionary As Object
Dim dictionaryKey As Variant
Dim i As Long
Set objDictionary = CreateObject("Scripting.Dictionary")

For i = 1 To lo.ListRows
    objDictionary.Item(CStr(lo.ListColumns("Record").DataBodyRange.Rows(i))) = objDictionary.Item(CStr(lo.ListColumns("Record").DataBodyRange.Rows(i))) + 1
Next i

For Each dictionaryKey In objDictionary.keys
    ' Do something
Next dictionaryKey

I have used a dictionary object instead of a normal collection object as it should do what you are trying to do. Because the item is incremented each time, you can also return the count of each item by using

objDictionary.item(dictionaryKey)

Collectives™ on Stack Overflow

Excel VBA - count number of different parameters in table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related