vba 每次用户单击命令按钮时，在 excel 中插入新行数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26484882/
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
Inserting new row of data in excel, every time user click on command button
提问by Nitish Gaurav
I have started learning VBA programming and thought of creating one small application for inserting student details into an Excel sheet. In one Excel sheet named "Main", I have created a form to take user inputs and in another sheet named "Database" I am trying to insert a row of records every time a user clicks the button.
我已经开始学习 VBA 编程并考虑创建一个小型应用程序，用于将学生详细信息插入 Excel 工作表中。在一个名为“Main”的 Excel 工作表中，我创建了一个表单来接受用户输入，而在另一个名为“Database”的工作表中，我试图在用户每次单击按钮时插入一行记录。
I am successfully able to insert one row of data i.e. first row of data in the database sheet, but my problem is - I want to go to the next row in the sheet once user enters the record and clicks on the button a second time. Similarly for the third time and so on.
我能够成功插入一行数据，即数据库表中的第一行数据，但我的问题是 - 一旦用户输入记录并再次单击按钮，我想转到表中的下一行。第三次以此类推。
My code is:
我的代码是：
Private Sub CommandButton1_Click()
Dim i As String
Dim j As String
Dim k As String
Dim l as integer
i = Sheets("Main").Cells(1, 2).Value
j = Sheets("Main").Cells(2, 2).Value
k = Sheets("Main").Cells(3, 2).Value
Sheets("Main").Cells(1, 2).Value = ""
Sheets("Main").Cells(2, 2).Value = ""
Sheets("Main").Cells(3, 2).Value = ""
l=2
Sheets("Database").Cells(l, 1).Value = i
Sheets("Database").Cells(l, 2).Value = j
Sheets("Database").Cells(l, 3).Value = k
End Sub
I want to increment value of l by 1, every time user clicks on the command button so that the new record is inserted into the next row of the Database sheet.
我想每次用户单击命令按钮时将 l 的值增加 1，以便将新记录插入到数据库表的下一行。
回答by Nitish Gaurav
We can get the last row of a particular column by :-
我们可以通过以下方式获取特定列的最后一行：-
dstRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
dstRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
Private Sub CommandButton1_Click()
Dim i As String
Dim j As String
Dim k As String
Dim m As String
i = Sheets("Main").Cells(1, 2).Value
j = Sheets("Main").Cells(2, 2).Value
k = Sheets("Main").Cells(3, 2).Value
m = Sheets("Main").Cells(4, 2).Value
Sheets("Main").Cells(1, 2).Value = ""
Sheets("Main").Cells(2, 2).Value = ""
Sheets("Main").Cells(3, 2).Value = ""
Sheets("Main").Cells(4, 2).Value = ""
dstRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Database").Cells(dstRw, 1).Value = i
Sheets("Database").Cells(dstRw, 2).Value = j
Sheets("Database").Cells(dstRw, 3).Value = k
Sheets("Database").Cells(dstRw, 4).Value = m
End Sub
Thanks :- Nitish Gaurav
谢谢 :- Nitish Gaurav
回答by quantum285
Quite confused about what is you want. How is the user inputting the next row? Also at the top of your code (where you assign values to i,j and k) you are iterating the rowand essentially copying a column, not a row.
很困惑你想要什么。用户如何输入下一行？同样在代码的顶部（您将值分配给 i、j 和 k），您正在迭代行并基本上复制一列，而不是一行。
I'll try to answer the remainder of your question as best I can. To make VBA know how to copy data to the next row you can do this:
我会尽量回答你剩下的问题。要让 VBA 知道如何将数据复制到下一行，您可以执行以下操作：
Sub test()
i = "John"
j = "25"
k = "Male"
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Cells(lastRow + 1, 1) = i
Cells(lastRow + 1, 2) = j
Cells(lastRow + 1, 3) = k
End Sub
Try running that a couple of times in a row and you'll see how it works. If I misunderstood please clarify what you want.
尝试连续运行几次，你会看到它是如何工作的。如果我误解了，请澄清你想要什么。
