Insert new row OR update existing row based on current cell value and textbox value

Question

My current code used to insert new data from userform. There's sometimes the data already exist in the current listing. There's another code that will decide to select new empty row or select existing row (with data) then defined the CurrentRow value. When this happen, I want my code be able to check if the textbox contain data (not empty) or not then update this data into existing row that already contained data. Existing data will be replace by new data from textbox. Skip those empty textbox.

So,

If activecell value is not empty and textbox not empty > replace with new data from textbox

If activecell value is empty insert whatever data from textbox into new row.

Private Sub OKButton_Click()
Dim CurrentRow As Long
Dim ctl As Control
Dim ChkBox As String

'Check for CheckBox value
    For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" And ctl = True Then
            ChkBox = ctl.Caption & " " & ChkBox
        End If
    Next ctl
''create new entry
CurrentRow = ActiveCell.Row
Cells(CurrentRow, 2).Value = TitleTextBox.Value
Cells(CurrentRow, 3).Value = AuthorTextBox.Value
Cells(CurrentRow, 4).Value = CopyTextBox.Value
Cells(CurrentRow, 5).Value = ISBNTextBox.Value
Cells(CurrentRow, 6).Value = CallNoTextBox.Value
Cells(CurrentRow, 7).Value = PublicationTextBox.Value
Cells(CurrentRow, 8).Value = ChkBox

Call UserForm_Initialize

End Sub

UPDATE: Code below decide which row to select.

Sub DupCheck(txt, ColNo As Long, theLabel As Object)
    Dim m
    With Worksheets("Booklist")
        m = Application.Match(txt, .Columns(ColNo), 0)
        If Not IsError(m) Then
            theLabel.Caption = "Duplicate" & " " & .Cells(m, ColNo).Address
            .Activate
            .Rows(m).Select
        Else
            theLabel.Caption = ChrW(&H2713)
            .Activate
            .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

        End If
    End With
End Sub

score 0 · Answer 1 · answered Mar 07 '20 at 04:48

'emptyRow = ws.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row

I guess you are using ISBN (Column 5) as a benchmark to look for an existing value. If not then change the code slightly below. To find an existing data you can use .FIND.

Is this what you are trying? (Untested)

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, WriteToRow As Long
    Dim aCell As Range

    '~~> Change this to the relevant sheet
    Set ws = Sheet1

    With ws
        '~~> Find last row in Column E
        lRow = .Range("E" & .Rows.Count).End(xlUp).Row

        '~~> Search the records in Col E for ISBN
        Set aCell = .Range("E1:E" & lRow).Find(What:=ISBNTextBox.Value, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            '~~> If text found use that row
            WriteToRow = aCell.Row
        Else
            '~~> If text not found use last row + 1
            WriteToRow = lRow + 1
        End If

        .Cells(WriteToRow, 2).Value = TitleTextBox.Value
        .Cells(WriteToRow, 3).Value = AuthorTextBox.Value
        .Cells(WriteToRow, 4).Value = CopyTextBox.Value
        .Cells(WriteToRow, 5).Value = ISBNTextBox.Value
        .Cells(WriteToRow, 6).Value = CallNoTextBox.Value
        .Cells(WriteToRow, 7).Value = PublicationTextBox.Value
        .Cells(WriteToRow, 8).Value = ChkBox
    End With
End Sub

Dear Siddharth, Thank you for your reply, I have update the info at the posting. Im really sorry because forgot to remove the _commented_ line. So sorry again. — amein, Mar 07 '20 at 06:08
Avoid using `.Select/Activate`. You may want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) Also did you try the code that I gave above? — Siddharth Rout, Mar 07 '20 at 06:16
Dear Siddharth, thank you again for pointing that out. And I'm sorry for late reply because I'm on holiday. Tested your code and its working. What if I want to expand to 3 column to search the record instead of 1 column only? Meaning that, if data in all this three column not found, then only select new row.. — amein, Mar 11 '20 at 07:25
You change `.Range("E1:E" & lRow)` to the relevant columns `.Range("E1:G" & lRow)` to include 3 columns.. E,F, and G — Siddharth Rout, Mar 11 '20 at 11:02

Insert new row OR update existing row based on current cell value and textbox value

1 Answers1