我怎么能算不同的值(数字和字符串的混合)在选定的(大)的数量范围在VBA?
How can I count the number of different values (numbers and strings mixed) in a chosen (large) range in VBA?
我觉得这个过程是这样:
1.读取数据为一维数组。
2.排序阵列(快或归并排序),需要测试这
3.简单地计算不同值的数量,如果排序的数组:若(a [1] - ;>在[我+ 1]),然后反=计数器+ 1
。
I think about this in this way:
1. Read in data into one dimensional array.
2. Sort array (quick or merge sort) need to test which
3. Simply count number of different values if sorted array : if(a[i]<>a[i+1]) then counter=counter+1
.
它是解决这一问题的最有效方法是什么?
Is it the most efficient way to solve this problem?
编辑:我想这样做在Excel中
I want to do it in Excel.
下面是一个VBA解决方案
Here is a VBA Solution
您不需要阵列来完成这件事。你也可以使用一个集合。示例
You don't need an Array to get this done. You can also use a collection. Example
Sub Samples()
Dim scol As New Collection
With Sheets("Sheet1")
For i = 1 To 100 '<~~ Assuming the range is from A1 to A100
On Error Resume Next
scol.Add .Range("A" & i).Value, Chr(34) & _
.Range("A" & i).Value & Chr(34)
On Error GoTo 0
Next i
End With
Debug.Print scol.Count
'For Each itm In scol
' Debug.Print itm
'Next
End Sub
跟进
Sub Samples()
Dim scol As New Collection
Dim MyAr As Variant
With Sheets("Sheet1")
'~~> Select your range in a column here
MyAr = .Range("A1:A10").Value
For i = 1 To UBound(MyAr)
On Error Resume Next
scol.Add MyAr(i, 1), Chr(34) & _
MyAr(i, 1) & Chr(34)
On Error GoTo 0
Next i
End With
Debug.Print scol.Count
'For Each itm In scol
' Debug.Print itm
'Next
End Sub