每次用户单击命令按钮时，在excel中插入新的数据行 [英] Inserting new row of data in excel, every time user click on command button

问题描述

我已经开始学习VBA编程，并考虑创建一个用于将学生详细信息插入Excel工作表的小型应用程序.在一个名为"Main"的Excel工作表中，我创建了一个表单来接受用户输入，在另一个名为"Database"的工作表中，我试图每次用户单击按钮时插入一行记录.

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.

我能够成功插入一行数据，即数据库表中的第一行数据，但是我的问题是-用户输入记录并单击按钮a后，我想转到表中的下一行第二次.第三次类似，依此类推.

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.

我的代码是:

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

每次用户单击命令按钮时，我希望将l的值增加1，以便将新记录插入数据库表的下一行.

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.

推荐答案

我们可以通过以下方式获取特定列的最后一行:-

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

谢谢:-尼蒂什·高拉夫(Nitish Gaurav)

Thanks :- Nitish Gaurav

这篇关于每次用户单击命令按钮时，在excel中插入新的数据行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！