Command button to search and replace value in table

[Electech99]

Hello, I'm trying to build a document to update serial numbers in a table that has around 1000 total entries.
I've created the table and the drop lists to load the information for the current serial number and that works as it should.

I've created the button and tried using google to get it programmed for a search and replace function but it keeps giving me an error, no matter how I input the values.
The table and the button are on different sheets.
Can someone please tell me what I'm doing wrong?

 

[eiloken]

Hello, I'm trying to build a document to update serial numbers in a table that has around 1000 total entries.
I've created the table and the drop lists to load the information for the current serial number and that works as it should.

I've created the button and tried using google to get it programmed for a search and replace function but it keeps giving me an error, no matter how I input the values.
The table and the button are on different sheets.
Can someone please tell me what I'm doing wrong?View attachment 101818

try this:

Sub UpdateSerial()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim lr As Long
    Dim rng As Range, cll As Range
    Dim oldSer As Long, newSer As Long
    Dim count As Integer
    oldSer = ThisWorkbook.Sheets("Update").Range("E16")
    newSer = ThisWorkbook.Sheets("Update").Range("E18")
    lr = ThisWorkbook.Sheets("Lists").Range("M" & Rows.count).End(xlUp).Row 'find last row cof column M, sheet Lists
    Set rng = ThisWorkbook.Sheets("Lists").Range("M6:M" & lr)
    For Each cll In rng 'loop to find and replace serial
        If cll.Value = oldSer Then
            cll.Value = newSer
            count = count + 1
        End If
    End If
    MsgBox count & "value has been replaced", vbInformation
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[Electech99]

Eiloken, thank you for the help!!!
I'm getting this error now.

 

[eiloken]

Eiloken, thank you for the help!!!
I'm getting this error now.View attachment 101824

oh sorry, that's my mistake, you need change "end if" to "next cll"

 

[Electech99]

Eiloken, thank you for the help!!! Very much appreciated!

 

[Electech99]

Eiloken, would there be a way to add code to search the list for the updated number and only change the value if that number does not already exist?

 

[eiloken]

Eiloken, would there be a way to add code to search the list for the updated number and only change the value if that number does not already exist?

in that case, if updated value wasn't exists, we can only change the first value found in the list, is that true?

 

[Electech99]

That is correct. If "new serial number" isn't found I want it to update. If it finds "new serial number already in the list, I don't want it to update. That would mean I have 2 identical serial numbers and that would be a problem.

 

[eiloken]

That is correct. If "new serial number" isn't found I want it to update. If it finds "new serial number already in the list, I don't want it to update. That would mean I have 2 identical serial numbers and that would be a problem.

so you can try this:

Sub UpdateSerial()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim lr As Long
    Dim rng As Range, cll As Range
    Dim oldSer As Long, newSer As Long
    oldSer = ThisWorkbook.Sheets("Update").Range("E16")
    newSer = ThisWorkbook.Sheets("Update").Range("E18")
    lr = ThisWorkbook.Sheets("Lists").Range("M" & Rows.count).End(xlUp).Row
    Set rng = ThisWorkbook.Sheets("Lists").Range("M6:M" & lr)
    If rng.Find(newSer, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) Is Nothing Then 'check that new serial existed
        For Each cll In rng
            If cll.Value = oldSer Then
                cll.Value = newSer
                Exit For 'Replace first found value and exit
            End If
        Next cll
        MsgBox "Match value has been replaced", vbInformation
    Else
        MsgBox "Serial already existed", vbInformation
    End If
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[Electech99]

Eiloken, that works perfectly!!! You are the best! Thank you again my friend!