从A19行开始，而不是从A1行开始

Question

我正在尝试一个输入数据用户表单，其中输入从"开始；A19"；并且将在行"0"处结束；A30"；一旦空行被1乘1填充而不是在"1"处开始的当前情况；A1〃；并且不受限制。

Private Sub cmdAdd_Click()
Dim wks As Worksheet
Dim AddNew As Range
Set wks = ActiveSheet
Set AddNew = wks.Range("A2:A65565").End(xlUp).Offset(1, 0)
AddNew.Offset(0, 2).Value = txtCAC.Text
AddNew.Offset(0, 4).Value = txtName.Text
AddNew.Offset(0, 5).Value = txtType.Text
AddNew.Offset(0, 6).Value = txtClass.Text
AddNew.Offset(0, 7).Value = txtDate.Text
AddNew.Offset(0, 8).Value = txtParent.Text
AddNew.Offset(0, 9).Value = txtManagement.Text
AddNew.Offset(0, 10).Value = txtSuccess.Text
AddNew.Offset(0, 12).Value = txtPercentage.Text
AddNew.Offset(0, 21).Value = txtCommittment.Text
AddNew.Offset(0, 38).Value = txtContribution.Text
AddNew.Offset(0, 40).Value = txtRedemption.Text
lstDisplay.ColumnCount = 41
lstDisplay.RowSource = "A2:A65356"
End Sub

Answer 1

显然，您的目标是覆盖相邻的固定目标范围有了一套完整的文本框条目，我建议采取以下步骤：

• [0.]定义目标小区中的固定起始小区，例如通过set tgt = Sheet1.Range("A19")
• [1. a)]Split一个所需文本框名称的列表，从而得到一个一维数组，btw将自动基于0
• [1. b)]通过二维数据数组提供数据，并使其基于零，以便在下面的循环(1.c(中同步两个数组计数器
• [1. c)]用For填充数据数组行中的所有文本框内容。。Next回路并检查不允许的零长度输入；如果有，则显示一条警告消息，并将焦点重定向到空文本框
• [2.]最终将数据阵列转储回选定的目标范围通过使用列出的文本框的数量来控制CCD_ 10到CCD_

Private Sub cmdAdd_Click()
'0. Define fixed start cell in target range
dim tgt as Range
set tgt = Sheet1.Range("A19")                ' change to any wanted sheet Code(Name)
'1. a) split a list of needed textbox names getting a 1-dim 0-based array automatically
Dim myTextboxes As Variant
myTextboxes = Split( _
"txtCAC,txtName,txtType,txtClass,txtDate,txtParent,txtManagement," & _
"txtSuccess,txtPercentage,txtCommittment,txtContribution,TxtRedemption", _
",")
Dim cnt As Long
cnt = UBound(myTextboxes) + 1                ' count number of textboxes
'   b) provide for data by a 2-dim data array (make it zero-based, too)
Dim data As Variant
ReDim data(0 To cnt - 1, 0 To 0)             ' define dimensions holding data
'   c) fill data array rows with all textbox contents
Dim i As Long, ctrl As MSForms.Control
For i = LBound(data) To UBound(data)         ' i.e. 0 To 11
Set ctrl = Me.Controls(myTextboxes(i))   ' set control to memory
data(i, 0) = ctrl.Text                   ' get textbox content

' check for complete entries or exit sub
If Len(Trim(data(i, 0))) = 0 Then        ' check for zero-length input
MsgBox "Fill in empty Textbox(es) first!", vbExclamation, ctrl.Name
ctrl.SetFocus                        ' set focus to empty box
Exit Sub                             ' escape procedure
End If
Next
'2. dump data to target range               
tgt.Resize(cnt, 1) = data             ' write data
End Sub

进一步提示

我认为需要定义一个RowSource(顺便说一句，如果你无论如何都要通过命令按钮覆盖所有数据，最好使用"Sheet1!A19:A30"

旁注：更喜欢通过例如Sheet1.Range("A" & .Rows.Count).End(xlUp)获得最后一行单元格或通过.Range("A" & .Rows.Count).End(xlUp).Row获得行索引，而不是编码固定的行数(当前工作表的行数约为100万(。您可能有兴趣阅读"查找最后使用的单元格"`

Answer 2

Dim wks As Worksheet
Dim AddNew As Range
Set wks = ActiveSheet
Dim h As Integer
h = wks.Range("C65356").End(xlUp).Row
If h < 18 Then h = 18
if h > 30 then exit Sub
Set AddNew = wks.Range("A" & h)
AddNew.Offset(1, 2).Value = txtCAC.Text
AddNew.Offset(1, 4).Value = txtName.Text
AddNew.Offset(1, 5).Value = txtType.Text
AddNew.Offset(1, 6).Value = txtClass.Text
AddNew.Offset(1, 7).Value = txtDate.Text
AddNew.Offset(1, 8).Value = txtParent.Text
AddNew.Offset(1, 9).Value = txtManagement.Text
AddNew.Offset(1, 10).Value = txtSuccess.Text
AddNew.Offset(1, 12).Value = txtPercentage.Text
AddNew.Offset(1, 21).Value = txtCommittment.Text
AddNew.Offset(1, 38).Value = txtContribution.Text
AddNew.Offset(1, 40).Value = txtRedemption.Text
'lstDisplay.ColumnCount = 41
'lstDisplay.RowSource = "A2:A65356"