excel - 在 VBA 中跨多个子项使用字典
问题描述
我有一本在一个子目录中定义的字典,我想用它来从位于不同子目录中的键中获取值。
Private Sub CommandButton2_Click()
ID = Me.TextBox1.Value
Dim rwd As Worksheet
Set rwd = ThisWorkbook.Sheets("Raw Data")
If Application.WorksheetFunction.CountIf(summ.Range("A:A"), Me.TextBox4.Value) > 0 Then
Dim rowsDict As Object
Set rowsDict = CreateObject("Scripting.Dictionary")
Dim counter As Long
counter = 1
For i = 2 To rwd.Range("A" & Application.rows.Count).End(xlUp).Row
If rwd.Range("A" & i).Value = ID Then
RowAndNumber = CStr(counter) & "-" & (rwd.Range("J" & i).Value)
Me.ComboBox3.AddItem (RowAndNumber)
rowsDict.Add RowAndNumber, i '
counter = counter + 1
End If
Next
End Sub
Private Sub ComboBox3_Change()
n = rowsDict.Item(Me.ComboBox3.Value)
Me.bTextBox131.Value = rwd.Range("J" & n).Value
End Sub
解决方案
您需要在模块范围内声明字典
' Move your declaration to outside your Sub
Dim rowsDict As Object
Private Sub CommandButton2_Click()
ID = Me.TextBox1.Value
Dim rwd As Worksheet
Set rwd = ThisWorkbook.Sheets("Raw Data")
If Application.WorksheetFunction.CountIf(summ.Range("A:A"), Me.TextBox4.Value) > 0 Then
Set rowsDict = CreateObject("Scripting.Dictionary")
Dim counter As Long
counter = 1
For i = 2 To rwd.Range("A" & Application.rows.Count).End(xlUp).Row
If rwd.Range("A" & i).Value = ID Then
RowAndNumber = CStr(counter) & "-" & (rwd.Range("J" & i).Value)
Me.ComboBox3.AddItem (RowAndNumber)
rowsDict.Add RowAndNumber, i '
counter = counter + 1
End If
Next
' You're also missing an End If
End If
End Sub
Private Sub ComboBox3_Change()
n = rowsDict.Item(Me.ComboBox3.Value)
Me.bTextBox131.Value = rwd.Range("J" & n).Value
End Sub
推荐阅读
- angular - 在Angular 7中填充迭代对象的数组
- r - 列出给定函数内的所有函数
- jenkins - 詹金斯管道脚本的问题。没有方法签名适用于参数类型:(java.lang.String) values[string]
- angular - Angular2 单元测试
- websocket - 关于 IE 中 websocket 数量限制的解决方法
- sql - 一个帐号与两个不同的 CIN 关联且两个 CIN 的后四位应相同的搜索表
- angular - 具有响应式表单的自定义控件
- php - 优化模型查询
- c# - 如何在winform janus网格中专注于过滤器行的一列
- ionic-framework - 在 ionic v4 中添加自定义加载微调器