时间戳

首页 > 解决方案 > 将数据输入工作表的命令按钮

excel - 将数据输入工作表的命令按钮

问题描述

我希望你们在假期后都过得愉快。只是有一个小问题。我创建了一个包含多个条目的用户表单，供用户将数据输入到工作表中，但是，我在设置命令按钮以将数据输入到工作表中时遇到了一点困难。

编码：

Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
    .Cells(3Row, 3).Value = Me.txtDate.Value
    .Cells(ERow, 4).Value = Me.cboType.Value
    .Cells(FRow, 5).Value = Me.cboDesciption.Value
    .Cells(GRow, 6).Value = Me.txtIncomeAmount.Value
    .Cells(HRow, 7).Value = Me.txtExpensesAmount.Value
    .Cells(IRow, 7).Value = Me.txtComment.Value
    End With
    'Clear Input Controls.
    Me.txtDate = ""
    Me.cboType = ""
    Me.cboDesciption = ""
    Me.txtIncomeAmount = ""
    Me.txtExpensesAmount = ""
    Me.txtComment = ""

我几乎没有编码知识，到目前为止，我已经在网上学习了很多不同的教程。任何帮助或方向将不胜感激。：） 提前致谢！！

标签： excelvba

解决方案

用作参数lRow的行引用.Cells()。试试下面的子。

Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
    .Cells(lRow, 3) = Me.txtDate
    .Cells(lRow, 4) = Me.cboType
    .Cells(lRow, 5) = Me.cboDesciption
    .Cells(lRow, 6) = Me.txtIncomeAmount
    .Cells(lRow, 7) = Me.txtExpensesAmount
    .Cells(lRow, 8) = Me.txtComment
End With

    'Clear Input Controls.
    Me.txtDate = ""
    Me.cboType = ""
    Me.cboDesciption = ""
    Me.txtIncomeAmount = ""
    Me.txtExpensesAmount = ""
    Me.txtComment = ""
End Sub

#EIDT

如果是表格，您需要找到特定列的最后一个空单元格。根据附件，您需要找到B Column均值Date列的最后一个空单元格。因此，请使用以下代码为您的工作表工作。

Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim rw As Integer
Dim ws As Worksheet
Dim lrow As Long
Dim LO As ListObject
Dim LEO As Range

Set ws = Worksheets("2020_Data")
Set LO = ws.ListObjects("Table2")
'lrow = ws.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row

With LO.Range.Columns(2)
    Set LEO = .Find(what:="", after:=.Cells(1), LookIn:=xlValues, _
        searchorder:=xlByRows, searchdirection:=xlNext)
    If Not LEO Is Nothing Then
        lrow = LEO.Row
'        MsgBox LEO.Row 'First empty row at column B
    End If
End With

'rw = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
    .Cells(lrow, "B") = Me.txtDate
    .Cells(lrow, "E") = Me.cboType
    .Cells(lrow, "F") = Me.cboDesciption
    .Cells(lrow, "G") = Me.txtIncomeAmount
    .Cells(lrow, "H") = Me.txtExpensesAmount
    .Cells(lrow, "I") = Me.txtComment
End With

    'Clear Input Controls.
    Me.txtDate = ""
    Me.cboType = ""
    Me.cboDesciption = ""
    Me.txtIncomeAmount = ""
    Me.txtExpensesAmount = ""
    Me.txtComment = ""
End Sub

推荐阅读