Filling Data with VBA Code by User Form in Certain Row

Question

I have a case in this VBA Code.

Basically, I have 2 sheets:

• Lending & Funding
• MUFG Client

What I want to do is adding a data in Lending & Funding sheet starts from A7507 horizontally and in MUFG Client starts from A103 as well.

This is the VBA code that I have so far

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

The thing is whenever I add data in MUFG Client, it will fill row 3 not 103.

This is what happened when you add data(s) in Lending & Funding

It Works!

But when I add data(s) in MUFG Client

Instead of row 103, The data(s) was added in row 3. It Doesn't Work!

Any help would be greatly appreciated.

Thank you so much in advance.

Answer 1

Your request is a little confusing, as we can't see your sheets. But here is an attempt at rewriting your code to :

1. Not use Activate.
2. Remove need to enter Sheet name.
3. Force the lastrow value to a minimum based on the sheet selected.
4. Not add part of the record if user doesn't want record added.
5. Write to debug window what was written to assist debugging.

---

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