Excel VBA +查找与字典 [英] Excel VBA + lookup with Dictionary
问题描述
我检查过我的Dict已经加载了我想要的所有数据,包括必要的偏移值。 (被评论的下一个循环)
我现在的问题是,当找到Dic.Exists(profitCentre)时,如何提取'offset'值? p>
Dim cl As Range,Dic As Object
Set Dic = CreateObject(Scripting.Dictionary):Dic.Comparemode = vbTextCompare
与工作簿(wbPropListing).Sheets(住宅)
对于每个cl In .Range(E3:E& .Cells(Rows.Count,E)。End(xlUp ).b
如果不是Dic.Exists(cl.Value)然后Dic.Add cl.Value,cl.Offset(,1).Value
下一个cl
结束
with Workbooks(wbPropListing).Sheets(Fund& CoT)
对于每个cl In .Range(E2:E& .Cells(Rows.Count,E)。End(xlUp)。行)
如果不是Dic.Exists(cl.Value)然后Dic.Add cl.Value,cl.Offset(,1).Value
下一个cl
结束
'For i = 0 To Dic.Count - 1
'Debug.Print Dic.items()(i),Dic.keys()(i)
'Next i
i = 0
对于每个profitCentre在myProfitCentreArray
如果profitCentre<> 而不是利润中心喜欢#### 0000和Dic.Exists(profitCentre)然后
lookupFound = Dic.items()
结束如果
Debug.PrintIndex:& ;我& 利益中心:&利润中心查找:& lookupFound
i = i + 1
下一个利润中心
我通过vlookup而不是字典找到了。
但是,如果您通过字典知道答案,我也想学习。 p>
以下是通过vlookup完成的代码
profitCentre在myProfitCentreArray
如果profitCentre<> 而不是利润中心#### 0000然后
与工作簿(wbPropListing).Sheets(住宅)
设置lookupResRange = .Range(E3:F& .Cells( Rows.Count,E)。End(xlUp).row)
End with
With Workbooks(wbPropListing).Sheets(Fund& CoT)
Set lookupFundRange = .Range E2:F& .Cells(Rows.Count,E)。End(xlUp).row)
结束
lookupResult = Application.VLookup(CStr(profitCentre) ,lookupResRange,2,False)
如果IsError(lookupResult)然后
lookupResult = Application.VLookup(CStr(profitCentre),lookupFundRange,2,False)
如果IsError(lookupResult)Then MsgBox利润中心:&利润中心未找到
结束如果
myforecastSheetsIndex = myforecastSheetsIndex + 1
结束如果
下一个利润中心
I am trying to do a lookup with dictionary. However, I am not able to extract the 'offset' of the key.
I've checked that my Dict has loaded all of the data that I want, including the necessary 'offset' value. (the for next loop of the commented)
My question now is, how do extract the 'offset' value, when Dic.Exists(profitCentre) is found?
Dim cl As Range, Dic As Object
Set Dic = CreateObject("Scripting.Dictionary"): Dic.Comparemode = vbTextCompare
With Workbooks(wbPropListing).Sheets("Residential")
For Each cl In .Range("E3:E" & .Cells(Rows.Count, "E").End(xlUp).row)
If Not Dic.Exists(cl.Value) Then Dic.Add cl.Value, cl.Offset(, 1).Value
Next cl
End With
With Workbooks(wbPropListing).Sheets("Fund&CoT")
For Each cl In .Range("E2:E" & .Cells(Rows.Count, "E").End(xlUp).row)
If Not Dic.Exists(cl.Value) Then Dic.Add cl.Value, cl.Offset(, 1).Value
Next cl
End With
'For i = 0 To Dic.Count - 1
' Debug.Print Dic.items()(i), Dic.keys()(i)
'Next i
i = 0
For Each profitCentre In myProfitCentreArray
If profitCentre <> "" And Not profitCentre Like "####0000" And Dic.Exists(profitCentre) Then
lookupFound = Dic.items()
End If
Debug.Print "Index: " & i & " profit centre: " & profitCentre & " Lookup: " & lookupFound
i = i + 1
Next profitCentre
I found out by doing via vlookup instead of dictionary.
However, if you know the answer via dictionary, I'd like to learn too.
Below is the code that I've done via vlookup
For Each profitCentre In myProfitCentreArray
If profitCentre <> "" And Not profitCentre Like "####0000" Then
With Workbooks(wbPropListing).Sheets("Residential")
Set lookupResRange = .Range("E3:F" & .Cells(Rows.Count, "E").End(xlUp).row)
End With
With Workbooks(wbPropListing).Sheets("Fund&CoT")
Set lookupFundRange = .Range("E2:F" & .Cells(Rows.Count, "E").End(xlUp).row)
End With
lookupResult = Application.VLookup(CStr(profitCentre), lookupResRange, 2, False)
If IsError(lookupResult) Then
lookupResult = Application.VLookup(CStr(profitCentre), lookupFundRange, 2, False)
If IsError(lookupResult) Then MsgBox "Profit Centre: " & profitCentre & " Not Found"
End If
myforecastSheetsIndex = myforecastSheetsIndex + 1
End If
Next profitCentre
这篇关于Excel VBA +查找与字典的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!