按列循环数据

Question

按列循环数据

我一直在使用以下代码填充UserForm中的顺序文本框，其中每个数组都由列编译。在下面的单元格值序列中:-

For Draw 1 TxtBox1 = B5, TxtBox2 = C5, TxtBox3 = D5, TxtBox4 = E5, TxtBox5 = B6, TxtBox6 = C6 etc Through to Cell E8
For Draw 2 TxtBox1 = Y5, TxtBox2 = Z5, TxtBox3 = AA5, TxtBox4 = AB5, TxtBox5 = Y6, TxtBox6 = Z6 etc
Option Explicit
Dim ws As Worksheet
Dim lngCtrlLoop As Long
Dim lngRowLoop As Long
Dim tbCounter As Long
Dim vCols As Variant
Dim vCol As Variant
Dim DrawToColsDict As Object
Private Sub userForm_Initialize()
Set ws = Sheets("Sheet1")
End Sub
Private Sub cmdCallResult_Click()
Set DrawToColsDict = CreateObject("Scripting.Dictionary")

With DrawToColsDict
.Add "Draw 1", Array("B", "C", "D", "E")
.Add "Draw 2", Array("Y", "Z", "AA", "AB")
End With
With Me
vCols = DrawToColsDict(.cboDrawNumber.Value)
tbCounter = 1
For lngRowLoop = 5 To 14
For Each vCol In vCols
.Controls("txtBox" & tbCounter).Text = ws.Cells(lngRowLoop, vCol).Text
tbCounter = tbCounter + 1
Next
Next
End With
End Sub

我正在寻找代码，其中的信息保存在列，所以所有的文本框每"绘制"(Combox Value)将从单个列填充对于Draw 1 TxtBox1 = B5, TxtBox2 = B6, TxtBox3 = B7 TxtBox4 = B8 TxtBox5 = B9等For Draw 2 TxtBox1 = C5, TxtBox2 = C6, TxtBox3 = C7等

如果有类似的解决方案，我们将不胜感激

注意:我已经尝试使用"代码"来格式化此查询中的示例代码。选项，但是，这不会格式化所有的代码。

Answer 1

Option Explicit
Private Sub cmdCallResult_Click()
Dim DrawToColsDict As Object, ws As Worksheet
Dim vCol As Variant, tbCounter As Long
Set DrawToColsDict = CreateObject("Scripting.Dictionary")
With DrawToColsDict
.Add "Draw 1", "B"
.Add "Draw 2", "C"
End With

Set ws = Sheets("Sheet1")
With Me
vCol = DrawToColsDict(.cboDrawNumber.Value)
For tbCounter = 1 To 16
.Controls("txtBox" & tbCounter).Text = ws.Cells(tbCounter + 4, vCol).Text
Next
End With
End Sub