在标题之后的表格顶部添加行

Question

我想用表单向表中添加新数据。我让它在工作表底部添加数据。

我希望新信息位于顶部。

使用我的代码，它将数据发送到两个工作表，即"主页"工作表和在第一个组合框中选择的工作表。

Private Sub CommandButton1_Click()
TargetSheet = ComboBox1.Value
If TargetSheet = "" Then
Exit Sub
End If
Worksheets(TargetSheet).Activate
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow + 1, 1).Value = TextBox1.Value
ActiveSheet.Cells(lastrow + 1, 2).Value = TextBox2.Value
ActiveSheet.Cells(lastrow + 1, 3).Value = TextBox3.Value
ActiveSheet.Cells(lastrow + 1, 4).Value = TextBox4.Value
ActiveSheet.Cells(lastrow + 1, 5).Value = TextBox5.Value
ActiveSheet.Cells(lastrow + 1, 6).Value = TextBox6.Value
ActiveSheet.Cells(lastrow + 1, 7).Value = TextBox7.Value
ActiveSheet.Cells(lastrow + 1, 8).Value = TextBox8.Value
Worksheets("Home").Activate
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow + 1, 1).Value = ComboBox1.Value
ActiveSheet.Cells(lastrow + 1, 2).Value = TextBox1.Value
ActiveSheet.Cells(lastrow + 1, 3).Value = TextBox2.Value
ActiveSheet.Cells(lastrow + 1, 4).Value = TextBox3.Value
ActiveSheet.Cells(lastrow + 1, 5).Value = TextBox4.Value
ActiveSheet.Cells(lastrow + 1, 6).Value = TextBox5.Value
ActiveSheet.Cells(lastrow + 1, 7).Value = TextBox6.Value
ActiveSheet.Cells(lastrow + 1, 8).Value = TextBox7.Value
ActiveSheet.Cells(lastrow + 1, 9).Value = TextBox8.Value
ActiveSheet.Cells(lastrow + 1, 10).Value = Date
ActiveSheet.Cells(lastrow + 1, 11).Value = TimeValue(Now)
ActiveSheet.Cells(lastrow + 1, 12).Value = TextBox9.Value
MsgBox ("Item Added Successfully.")
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
Worksheets("Home").Activate
Worksheets("Home").Cells(1, 1).Select
End Sub

由于工作表上有标题，如何将新数据放在第二行？

Answer 1

有很多东西可以改进你的代码，但我想保持简单

一些事情要开始：

1. 使用显式选项，这样您就不会遇到未定义变量的意外行为
2. 始终缩进您的代码(请参阅 www.rubberduckvba.com 可以帮助您的免费工具(
3. 尝试分离定义变量的逻辑并重用它们
4. 为窗体的控件命名
5. 查看一篇关于用户窗体的精彩文章(当您觉得自己准备好前进时(

检查代码的注释，并对其进行调整以满足您的需求

编辑：不需要EntireRow限定符，因为我们已经选择了整行，并从下面添加了复制格式

法典：

Private Sub CommandButton1_Click()
' Define object variables
Dim targetSheet As Worksheet
Dim homeSheet As Worksheet
Dim targetSheetName As String
Dim homeSheetName As String
Dim targetSheetTopRow As Long
Dim homeSheetTopRow As Long
Dim textBox1Value As Variant
Dim textBox2Value As Variant
Dim textBox3Value As Variant
Dim textBox4Value As Variant
Dim textBox5Value As Variant
Dim textBox6Value As Variant
Dim textBox7Value As Variant
Dim textBox8Value As Variant
Dim textBox9Value As Variant
' Define parameters
targetSheetTopRow = 2
homeSheetTopRow = 2
homeSheetName = "Home"
' Validate if combobox has any value
If Me.ComboBox1.Value = vbNullString Then Exit Sub
' Get target sheet name
targetSheetName = Me.ComboBox1.Value
' Add a reference to sheets
Set targetSheet = ThisWorkbook.Worksheets(targetSheetName)
Set homeSheet = ThisWorkbook.Worksheets(homeSheetName)
' Store current controls values
textBox1Value = Me.TextBox1.Value
textBox2Value = Me.TextBox2.Value
textBox3Value = Me.TextBox3.Value
textBox4Value = Me.TextBox4.Value
textBox5Value = Me.TextBox5.Value
textBox6Value = Me.TextBox6.Value
textBox7Value = Me.TextBox7.Value
textBox8Value = Me.TextBox8.Value
' No need to activate stuff
With targetSheet
' Insert a row after row 2
.Range(targetSheetTopRow & ":" & targetSheetTopRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
' Add cells values
.Cells(targetSheetTopRow, 1).Value = textBox1Value
.Cells(targetSheetTopRow, 2).Value = textBox2Value
.Cells(targetSheetTopRow, 3).Value = textBox3Value
.Cells(targetSheetTopRow, 4).Value = textBox4Value
.Cells(targetSheetTopRow, 5).Value = textBox5Value
.Cells(targetSheetTopRow, 6).Value = textBox6Value
.Cells(targetSheetTopRow, 7).Value = textBox7Value
.Cells(targetSheetTopRow, 8).Value = textBox8Value
End With
With homeSheet
' Insert a row after row 2
.Range(homeSheetTopRow & ":" & homeSheetTopRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
' Add cells values
.Cells(homeSheetTopRow, 1).Value = textBox1Value
.Cells(homeSheetTopRow, 2).Value = textBox2Value
.Cells(homeSheetTopRow, 3).Value = textBox3Value
.Cells(homeSheetTopRow, 4).Value = textBox4Value
.Cells(homeSheetTopRow, 5).Value = textBox5Value
.Cells(homeSheetTopRow, 6).Value = textBox6Value
.Cells(homeSheetTopRow, 7).Value = textBox7Value
.Cells(homeSheetTopRow, 8).Value = textBox8Value
.Cells(homeSheetTopRow, 9).Value = Date
.Cells(homeSheetTopRow, 10).Value = TimeValue(Now)
.Cells(homeSheetTopRow, 11).Value = textBox9Value
End With
' Clear control's values
Me.TextBox1.Value = vbNullString
Me.TextBox2.Value = vbNullString
Me.TextBox3.Value = vbNullString
Me.TextBox4.Value = vbNullString
' Alert user
MsgBox ("Item Added Successfully.")
' Goto...
homeSheet.Activate
homeSheet.Cells(1, 1).Select
End Sub

让我知道它是否有效或您需要更多帮助