各位大大好：
小弟對dictionary的應用仍是停留在
一知半解。
現小弟對下列語法的應用無法突破
瓶頸，煩請各位大大能幫忙解惑。
語法如下：
Sub aa()
Dim mDic As Scripting.Dictionary
Dim mRng As Range, mRng1 As Range, mRng2 As Range, E As Range
Dim mSht As Worksheet
Dim mKey, mItem
Dim s%, s1%, m1%, m2%
Dim mTmp$, mTmp1$
Set mSht = Worksheets(1)
With mSht
Set mRng1 = .Range("e1:e17")
Set mRng2 = .Range("h1:h17")
Set mDic = CreateObject("scripting.dictionary")
For Each E In mRng1
If mDic.Exists(E.Value) = False Then
mDic(E.Value) = 1
Else
mDic(E.Value) = mDic(E.Value) + 1
End If
Next
For Each E In mRng2
If mDic.Exists(E.Value) = False Then
mDic(E.Value) = 1
Else
mDic(E.Value) = mDic(E.Value) + 1
End If
Next
mKey = mDic.Keys
mItem = mDic.Items
For s = 0 To mDic.Count - 1
If mKey(s) = "d" Then
m1 = mItem(s)
End If
If mKey(s) = "e" Then
m2 = mItem(s)
End If
Next
For s = 0 To mDic.Count
If mKey(s) = "f" Then
mItem(s) = mItem(s) + m1 + m2
Exit For
End If
Next
Set mRng = .Range("a1:a4")
For Each E In mRng '此語法如何修正才能取出正確的item值呢
E.Offset(, 1) = mDic(E.Value) '此語法無法取出變更後之item值而是取出變更之前的值呢？
Next
For s = 0 To mDic.Count - 1 '下列語法卻可取出變更後的item 值呢其原因為何？
Set mRng = .Range("a1:a4").Find(what:=mKey(s), lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
If Not mRng Is Nothing Then
mRng.Offset(, 1) = mItem(s)
End If
Next
End With
End Sub
謝謝各位大大。 |
發表於 2011-9-2 07:11
| 
