在特定行中通过用户表单使用VBA代码填充数据

debugcn 发表于 Dev

伦迪·朱利安（Rendi Julian）

我在此VBA代码中有一个案例。

基本上，我有2张纸：

贷款与资金
MUFG客户

我想做的是从A7507水平开始在Lending＆Funding工作表中添加数据，在MUFG Client中也从A103开始添加数据。

这是我到目前为止的VBA代码

Private Sub CommandButton1_Click()
whichSheet = InputBox("In which sheet do you wish to enter data? Specify Sheet as Lending & Funding or MUFG Client only.", "Sheet Name")
If whichSheet = "" Then
MsgBox "You didn't specify a sheet!"
Exit Sub
End If

Worksheets(whichSheet).Activate
Dim lastrow
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
 
lastrow = lastrow + 1
Cells(lastrow, 1) = TextBox1
If Application.WorksheetFunction.CountIf(Range("A7507:A" & lastrow), Cells(lastrow, 1)) > 1 Then
MsgBox "Duplicate Data! Only Unique CIFs allowed", vbCritical, "Remove Data", Cells(lastrow, 1) = ""
ElseIf Application.WorksheetFunction.CountIf(Range("A7507:A" & lastrow), Cells(lastrow, 1)) = 1 Then
answer = MsgBox("Are you sure you want to add the record?", vbYesNo + vbQuestion, "Add Record")
If answer = vbYes Then
Cells(lastrow, 1) = TextBox1.Text
Cells(lastrow, 2) = TextBox2.Text
Cells(lastrow, 3) = TextBox3.Text
Cells(lastrow, 4) = TextBox4.Text
Cells(lastrow, 5) = TextBox5.Text
Cells(lastrow, 6) = TextBox6.Text
Cells(lastrow, 7) = TextBox7.Text
Cells(lastrow, 8) = TextBox8.Text
Cells(lastrow, 9) = TextBox9.Text
Cells(lastrow, 10) = TextBox10.Text
Cells(lastrow, 11) = TextBox11.Text
Cells(lastrow, 12) = TextBox12.Text
Cells(lastrow, 13) = TextBox13.Text
Cells(lastrow, 14) = TextBox14.Text
Cells(lastrow, 15) = TextBox15.Text
End If
End If
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

问题是，每当我在MUFG Client中添加数据时，它将填充第3行而不是103。

这是在“借贷和资金”中添加数据时发生的情况

有用！

但是当我在MUFG Client中添加数据时

而不是第103行，而是在第3行中添加了数据。它不起作用！

任何帮助将不胜感激。

提前非常感谢您。

CLR

您的要求有些混乱，因为我们看不到您的床单。但是，这是尝试将您的代码重写为：

不使用Activate。
删除需要输入工作表名称。
lastrow根据所选工作表将值强制为最小值。
如果用户不希望添加记录，则不添加记录的一部分。
“写入调试窗口”为协助调试而编写的内容。

Private Sub CommandButton1_Click()

    Dim whichsheet As String
    whichsheet = InputBox("In which sheet do you wish to enter data? Enter 1 for Lending & Funding or 2 for MUFG Client", "Sheet selector")
    
    If whichsheet <> "1" And whichsheet <> "2" Then
       MsgBox "You didn't specify a valid sheet!"
       Exit Sub
    End If
    Dim firstusablerow As Long
    If whichsheet = "1" Then
       firstusablerow = 7507
       whichsheet = "Lending & Funding"
    Else
       firstusablerow = 103
       whichsheet = "MUFG Client"
    End If
    
    With Worksheets(whichsheet)
        Dim lastrow As Long
        lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        
        If lastrow < firstusablerow Then lastrow = firstusablerow
        
        If Application.WorksheetFunction.CountIf(.Range("A" & firstusablerow & ":A" & lastrow), .Cells(lastrow, 1)) > 0 Then
            MsgBox "Duplicate Data! Only Unique CIFs allowed", vbCritical, "Remove Data", .Cells(lastrow, 1) = ""
        Else
            answer = MsgBox("Are you sure you want to add the record?", vbYesNo + vbQuestion, "Add Record")
            If answer = vbYes Then
                Debug.Print "Writing " & TextBox1.Text & " to row " & lastrow & " on sheet " & whichsheet
                .Cells(lastrow, 1) = TextBox1.Text
                .Cells(lastrow, 2) = TextBox2.Text
                .Cells(lastrow, 3) = TextBox3.Text
                .Cells(lastrow, 4) = TextBox4.Text
                .Cells(lastrow, 5) = TextBox5.Text
                .Cells(lastrow, 6) = TextBox6.Text
                .Cells(lastrow, 7) = TextBox7.Text
                .Cells(lastrow, 8) = TextBox8.Text
                .Cells(lastrow, 9) = TextBox9.Text
                .Cells(lastrow, 10) = TextBox10.Text
                .Cells(lastrow, 11) = TextBox11.Text
                .Cells(lastrow, 12) = TextBox12.Text
                .Cells(lastrow, 13) = TextBox13.Text
                .Cells(lastrow, 14) = TextBox14.Text
                .Cells(lastrow, 15) = TextBox15.Text
            End If
        End If
    End With
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。