在Microsoft Excel VBA上压缩多个组合框编码

Question

我正在尝试压缩以下代码。我认为循环函数可能有效，但也可能不是因为 VBA 项目的差异。有什么见解吗？

If CheckBox1.Value = True _
Then
Range("P" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("P" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox2.Value = True _
Then
Range("Q" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("Q" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox3.Value = True _
Then
Range("R" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("R" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox4.Value = True _
Then
Range("S" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("S" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox5.Value = True _
Then
Range("T" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("T" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox6.Value = True _
Then
Range("U" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("U" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox7.Value = True _
Then
Range("V" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("V" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox8.Value = True _
Then
Range("W" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("W" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox9.Value = True _
Then
Range("X" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("X" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox10.Value = True _
Then
Range("Y" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("Y" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox11.Value = True _
Then
Range("Z" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("Z" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox12.Value = True _
Then
Range("AA" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("AA" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox13.Value = True _
Then
Range("AB" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("AB" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If
If CheckBox14.Value = True _
Then
Range("AC" & Rows.Count).End(xlUp).Offset(0, 0).Value = "Y"
Else
Range("AC" & Rows.Count).End(xlUp).Offset(0, 0).Value = "N"
End If

Answer 1

像这样：

Dim i As Long, ws As Worksheet
Set ws = ActiveSheet 'or whatever
For i = 1 To 14
ws.Cells(Rows.Count, "P").Offset(0, i - 1).End(xlUp).Value = _
IIf(Me.Controls("CheckBox" & i).Value = True, "Y", "N")
Next

不过，这不应该.Offset(0, 0).Offset(1, 0)吗？ 或者你只是覆盖了已经存在的值。

Answer 2

这不是一个完整的答案 - 但解决方案的骨架可能包括这种结构

Dim xCtrl As Object, dVal As Variant, xRng As Range
For Each xCtrl In Me.Controls
If Left(xCtrl.name, 8) = "CheckBox" Then
dVal = Val(Mid(xCtrl.name, 9))
If dVal >= 1 And dVal < 15 Then
If xCtrl.Value = True then
' ComboBox is True ... Update here
Else
' ComboBox is False ... Update here
End If
End If
End If
Next xCtrl