vba 如何将数据添加到excel中的特定行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14867133/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add data into particular row in excel
提问by mat duwe
I want to ask how to add data into into a particular row in an Excel spreadsheet using a Userform.
我想问一下如何使用Userform.
When I add a new product A to the sheet, I want the new data to be located under the last row of product A, and the same for other products B and C.
当我向工作表添加新产品 A 时，我希望新数据位于产品 A 的最后一行下方，其他产品 B 和 C 也是如此。
Is there any way to sort the data exactly when I enter the new data into the existing table?
当我将新数据输入到现有表中时，有没有办法对数据进行准确排序？
The code I have below adds data row by row, no matter what product I enter, making the table unsorted and appearing scattered.
我下面的代码逐行添加数据，无论我输入什么产品，都会使表格未排序并显得分散。
Private Sub CommandButton1_Click()
Dim LastRow As Object
Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
MsgBox "One record written to Sheet1"
If MsgBox("Do you want to continue entering data?", vbYesNo + vbCritical, "Caution") = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
Else
End
TextBox1.SetFocus
Unload Me
End If
End Sub
采纳答案by Siddharth Rout
You can add the data to the Last Row and then Sort the data.
您可以将数据添加到最后一行，然后对数据进行排序。
1)To get the last row see this link
1)要获得最后一行，请参阅此链接
2)To Sort the data you can use this simple code. The below sample code will sort Col A which has headers
2）要对数据进行排序，您可以使用这个简单的代码。下面的示例代码将对具有标题的 Col A 进行排序
With Sheets("Sheet1")
.Columns("A:A").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
3)One Tip about using End: See Point 6 in thisthread.
3)关于使用的一个提示End：请参阅此线程中的第 6 点。
4)So If I combine both codes then your code will look something like this
4）所以如果我结合两个代码那么你的代码看起来像这样
Option Explicit
Private Sub CommandButton1_Click()
Dim LastRow As Long
'~~> Change this to the releavnt sheet name
With Sheets("Sheet1")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Range("A" & LastRow) = TextBox1.Text
.Range("B" & LastRow) = TextBox2.Text
.Range("C" & LastRow) = TextBox3.Text
'~~> Sort the Data. I am assuming that Row 1 has Headers
.Columns("A:C").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
If MsgBox("One record written to Sheet1. Do you want to continue entering data?", vbYesNo + vbCritical, "Caution") = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If
End Sub
