Counting Items in Array

[Keppel]

Right, I'll be extremely thankful if someone can help me with this.

I've compiled an Array which is "FillArray", which is derived from a listbox. The Array has a number of values in, some of which are the same. It's basically a list of products, so there are, for example, 2 eggs, 2 packets of bread etc, but they're inputted as egg, egg, bread, bread.

I've been using this,

ArrayLength = (UBound(FillArray) + 1)

to return the overall number of items in the Array, but I'd like to know how to return the number of each items there are, so how can I make it say there are 2 eggs, and 2 breads !?!

Any help would be appreciated, Thanks.

[fixo]

Not sure about you can do it with pure VBA instead of
pass it in application module
Anyway, give this a atry


Public Function Groupping(Source As Variant) As Variant()
Dim i As Long, j As Long, n As Long
Dim col As Collection
Set col = New Collection
For i = LBound(Source) To UBound(Source)
On Error Resume Next
col.Add Source(i), Source(i)
Next
MsgBox col.Count
Dim elem As Variant
ReDim countArr(col.Count - 1, 1)
For Each elem In col
j = 0
For i = LBound(Source) To UBound(Source)
If elem = Source(i) Then
j = j + 1
End If
Next
countArr(n, 0) = elem
countArr(n, 1) = j
n = n + 1
Next
Groupping = countArr
Groupping_Error:
Groupping = Null
Exit Function
End Function
Sub test()
Dim ar
ar = Array("egg", "salt", "salt", "egg", "salt", "vodka", "bread", "bread", "salt", "bread", "salt")
Dim v() As Variant
v = Groupping(ar)
Dim i
Dim msg As String
msg = ""
For i = LBound(v, 1) To UBound(v, 1)
msg = msg & v(i, 0) & ": " & vbTab & v(i, 1) & vbCr
Next
MsgBox msg
End Sub


~'J'~

[Keppel]

Unfortunately I couldn't get it to work on my database, but, I did manage to use some of the Array codes to make it work! What I've had to do is, from a listbox of those products, seperate the list into individual items in one array and the number of each in a second array.

This code came in handy with understanding arrays better, Thankyou!

[fixo]

I thought better yet to display result in the
two columns listbox
See attachments

~'J'~

[CreganTur]

I'm pretty new to VBA and Access, but I was wondering if a solution could be reached for this by counting the number of times a given item is repeated in the list array?

I don't know if you can do a Count function on a string with VBA, but that's one way I would get the answer with vTask Studio.

[fixo]

I'm pretty new to VBA and Access, but I was wondering if a solution could be reached for this by counting the number of times a given item is repeated in the list array?

I don't know if you can do a Count function on a string with VBA, but that's one way I would get the answer with vTask Studio.

Hi, I agree with you
DCount function is works pretty well
See attachment, it may helps

~'J'~