Excel VBA UserForm - Update/Override Data

Question

I'm trying to pull and update some records thru an UserForm and I've almost got it, except for the piece of code that isn't working as intended. It's supposed to override the previous record (whatever information is found in the same row as the record found by the IDNum.

For some reason, it starts to copy the data as of column B when it should override everything as of Column A.

Here's the code.

Private Sub RecordUpdate_Click()
    Dim LastRow As Long
    Dim IDNum As String
    Dim rngIDNum As Range
    Dim WriteRow As Long
    Dim ws As Worksheet

    Set ws = Worksheets("Records")

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rngIDNum = .Range("E1:E" & LastRow)
        IDNum = txtID.Value
        WriteRow = Application.Match(IDNum, rngIDNum, 0)
        Cells(WriteRow, 1).Select
        With ActiveCell
            .Offset(0, 1).Value = txtName.Value
            .Offset(0, 2).Value = txtlName.Value
            .Offset(0, 3).Value = txtGender.Value
            .Offset(0, 4).Value = txtAge.Value
            .Offset(0, 5).Value = txtID.Value
        End With
    End With
End Sub

It leaves intact whatever is in Column A and copies the edited entries from B to F instead of A to E. Any inputs are greatly appreciated.

L42 · Accepted Answer · 2015-03-25 05:54:59Z

2

It should start with:

.Offset(0, 0).Value = txtName.Value

Setting the ColumnIndex to 1 will make it target 1 column to the right.
Also, instead of using Select you can directly work on the object like this:

With .Cells(WriteRow, 1)
    .Offset(0, 0).Value = txtName.Value
    .
    .
    .Offset(0, 4).Value = txtID.Value
End With

Or you can also try this version of your code using Range.Find Method.

Private Sub RecordUpdate_Click()
    Dim LastRow As Long
    Dim IDNum As String
    Dim rngIDNum As Range
    Dim ws As Worksheet

    Set ws = Worksheets("Records")
    ' Find the ID 
    With ws
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        IDNum = txtID.Value
        Set rngIDNum = .Range("E1:E" & LastRow).Find(IDNum, .Range("E" & LastRow))
    End With
    ' Inform and Exit if ID doesn't exist
    If rngIDNum Is Nothing Then MsgBox "ID not found": Exit Sub
    ' Update the values
    With rngIDNum
        .Offset(0, -4).Value = txtName.Value
        .Offset(0, -3).Value = txtlName.Value
        .Offset(0, -2).Value = txtGender.Value
        .Offset(0, -1).Value = txtAge.Value
        .Offset(0, 0).Value = txtID.Value
    End With
End Sub

I assumed you're working on Worksheet("Records") which you have assigned in ws variable.

edited Mar 25, 2015 at 5:54

answered Mar 25, 2015 at 5:44

L42

19.8k11 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BlueSun3k1 Over a year ago

Thanks for the prompt response and the help. I tried both options and only the first option worked. The second option with the modified version of the script generated an error. Run-time error 1004: Application-defined or Object-defined error. I'm going with the first option. Thanks again.

BlueSun3k1 Over a year ago

It points to .Offset(0, -4).Value = txtName.Value. I'm actually having issues with both scripts now. on the first option WriteRow = Application.Match(IDNum, rngIDNum, 0) is causing the script to crash "WriteRow" Returns 0 when I hover over it while debugging.

BlueSun3k1 Over a year ago

I'm trying your second option and I fixed the Run-time error 1004: Application-defined or Object-defined error. I had added a column before and after txtID.Value which was making it look into the wrong Offset numbers. However, the script passes with no error but it's not saving the changes I make to the data before I run the script. Any ideas why?

L42 Over a year ago

@user1582396 If no match is found, then it will not save the changes. If you are searching in Column E then there should be no problem with the code. I've tried and tested it.

Collectives™ on Stack Overflow

Excel VBA UserForm - Update/Override Data

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related