|
发表于 2013-1-27 11:22
|
显示全部楼层
本楼为最佳答案
本帖最后由 hwc2ycy 于 2013-1-27 11:23 编辑
对于百位为0,没有显示出来。
如果要显示出来在百位为0的情况,代码就用这个。- Option Explicit
- Sub 组合数()
- '---------------------------------------------------------------------------------------
- ' Procedure : 组合数
- ' Author : hwc2ycy
- ' Date : 2013/1/27
- ' Purpose :字典与数组的应用
- '---------------------------------------------------------------------------------------
- '
- Dim arr
- Dim arr2()
- Dim arr3(1 To 3)
- Dim dic As Object
- Dim i&, j&, k&, x, y, z
- Dim Check As Boolean
- '读数据到字典,因为用的是区域法,所以要确保在数字区域
- '无其他内容
- arr = Range("c1").CurrentRegion
- '字典
- Set dic = CreateObject("scripting.dictionary")
- '遍历数组,2行一次
- For i = LBound(arr) + 1 To UBound(arr) Step 2
- For j = LBound(arr, 2) To UBound(arr, 2)
- '如果数据为非数字(或者为0)的情况下,则跳出本轮循环
- If Not (Val(arr(i, j)) > 0 And Val(arr(i + 1, j)) > 0) Then GoTo quit
- Call splitnumber(arr(i, j), dic)
- Call splitnumber(arr(i + 1, j), dic)
- Call AnyDic(dic)
- '如果没有共同数据,则结束本轮循环,字典清空
- If dic.Count = 0 Then
- quit:
- Check = False
- dic.RemoveAll
- Exit For
- End If
- arr3(j) = dic.keys
- '能否进行组合的前提条件
- Check = True
- dic.RemoveAll
- Next
- If Check Then
- '遍历,生成组合,x,y,z分别代表百,拾,个位
- For Each x In arr3(1)
- For Each y In arr3(2)
- For Each z In arr3(3)
- '动态数组
- k = k + 1
- ReDim Preserve arr2(1 To k)
- arr2(k) = "'" & Format(x * 100 + y * 10 + z, "000")
- Next
- Next
- Next
- End If
- Next
- Columns("m").Clear
- If k > 0 Then
- '一维变二维,写回M列
- Range("m1").Resize(k) = WorksheetFunction.Transpose(arr2)
- MsgBox "提取完成"
- Else
- MsgBox "数据中无相同数据,组合结果为0"
- End If
- Set dic = Nothing
- End Sub
- Sub splitnumber(ByVal number, ByRef dic As Object)
- '---------------------------------------------------------------------------------------
- ' Procedure : splitnumber
- ' Author : hwc2ycy
- ' Date : 2013/1/27
- ' Purpose :把数字拆分装进字典
- '---------------------------------------------------------------------------------------
- '
- Dim i, s$
- For i = 1 To Len(number)
- s = Mid(number, i, 1)
- dic(s) = dic(s) + 1
- Next
- End Sub
- Sub AnyDic(ByRef dic As Object)
- '---------------------------------------------------------------------------------------
- ' Procedure : AnyDic
- ' Author : hwc2ycy
- ' Date : 2013/1/27
- ' Purpose :分析字典,去掉没有重复数的
- '---------------------------------------------------------------------------------------
- '
- Dim key
- For Each key In dic.keys
- If dic(key) = 1 Then dic.Remove key
- Next
- End Sub
复制代码 |
|