Worksheet change event to edit data range using lookuplist update

devofish

Board Regular
Joined
Dec 10, 2016
Messages
68
Hello. Just curious if this is possible - it seems like really anything is possible in vba if the data is structured right ;).

I have a lookuplist sheet that stores identifiers and a separate sheet that uses these unique identifiers as a group attribute. Consequently there can be tens potentially hundreds of duplicated identifiers in the sheet range. I've given the user the ability to edit the lookuplist identifiers if required, however that opens the possibility that 2 or more of the same identifiers however with different spelling or special characters, etc. coexist.

I've come across a couple things, however it mostly involves comparing cell values where as the lookuplist is a list and the sheet range data is exponentially larger. Is there a function that could update all the values on the separate sheet if a user edits the original identifier? I would think it could be something within a regular module that the update routine could call. Could anybody point me in the right direction?
 
Put the code from the worksheet_change() under the commandbutton.
The Application.Undo can you change in userform1_initialize.
The rules with target can you delete.

Code:
Public Oldvalue As String


Private Sub UserForm_Initialize()
Oldvalue = Sheets("List").Range("A2")
End Sub


Private Sub CommandButton1_Click()
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("List")
    Dim ws2 As Worksheet
    Set ws2 = Worksheets("Data")
    Dim NewValue As Variant
    Dim SearchRange As Range
    Dim Found As Variant
    Dim FirstAddress As Variant
    Dim CountReplaces As Integer
    NewValue = UserForm1.TextBox1
    Set SearchRange = ws2.Range(Sheets(1).Cells(2, 1), Sheets(1).Cells(Rows.Count, 1).End(xlUp).Address)
    With SearchRange
        Set Found = .Find(Oldvalue, LookIn:=xlValues)
        If Not Found Is Nothing Then
            FirstAddress = Found.Address
            CountReplaces = 0
            Do
                ws2.Range(Found.Address) = NewValue
                CountReplaces = CountReplaces + 1
                Set Found = .FindNext(Found)
                If Found Is Nothing Then GoTo DoneFinding
            Loop While Found.Address <> FirstAddress
DoneFinding:
            If CountReplaces > 0 Then MsgBox (CountReplaces & " Items replaced!")
        End If
    End With
End Sub
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I'm sorry, that worked - as in no errors - but it is not updating the values. Below is the total update routine.
Code:
Private Sub cmdUpdate_Click()    
    
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("LookupList")
    Dim ws2 As Worksheet
    Set ws2 = Worksheets("Distributions")
    Dim NewValue As Variant
    Dim SearchRange As Range
    Dim Found As Variant
    Dim FirstAddress As Variant
    Dim CountReplaces As Integer
    NewValue = frmGroups.TextBox1
    
        If Me.ListBox1.ListIndex = -1 Then
             'msgbox code
        End If
    
       With ws1
            RowCurrent = Me.ListBox1.ListIndex + 2
            .Cells(RowCurrent, miCOL_NO__GROUP).Value = Me.TextBox1.Text
            .Cells.EntireColumn.AutoFit
        End With
        
    Set SearchRange = ws2.Range(Cells(2, 2), Cells(Rows.Count, 1).End(xlUp).Address)
    With SearchRange
        Set Found = .Find(OldValue, LookIn:=xlValues)
        If Not Found Is Nothing Then
            FirstAddress = Found.Address
            CountReplaces = 0
            Do
                ws2.Range(Found.Address) = NewValue
                CountReplaces = CountReplaces + 1
                Set Found = .FindNext(Found)
                If Found Is Nothing Then Exit Do
            Loop While Found.Address <> FirstAddress
            If CountReplaces > 0 Then MsgBox ("This group was replaced " & CountReplaces & " times within the distribution list.")
        End If
    End With
        TextBox1.Text = ""
End Sub
 
Last edited:
Upvote 0
I miss the code with OldValue.
Code:
[COLOR=#333333]            OldValue = .Cells(RowCurrent, miCOL_NO__GROUP).Value
[/COLOR][COLOR=#333333]            .Cells(RowCurrent, miCOL_NO__GROUP).Value = Me.TextBox1.Text
[/COLOR]
 
Upvote 0
Here is the final code:
Code:
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">
Private Sub cmdUpdate_Click()    

   [COLOR=#333333][FONT=Verdana]Dim OldValue As String
[/FONT][/COLOR]   Dim NewValue As Variant
   Dim SearchRange As Range
   Dim Found As Variant
   Dim FirstAddress As Variant
   Dim CountReplaces As Integer

   Set ws1 = Worksheets(1)
   Set ws2 = Worksheets(2)
   NewValue = frmGroups.TextBox1

      With ws1
         RowCurrent = Me.ListBox1.ListIndex + 2
         OldValue = ws1.Cells(RowCurrent, 1).Value
         .Cells(RowCurrent, 1).Value = Me.TextBox1.Text
      End With
      Set SearchRange = ws2.Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp).Address)
      With SearchRange
         Set Found = .Find(OldValue, LookIn:=xlValues)
         If Not Found Is Nothing Then
            FirstAddress = Found.Address
            CountReplaces = 0
         Do
            ws2.Range(Found.Address) = NewValue
            CountReplaces = CountReplaces + 1
            Set Found = .FindNext(Found)
         If Found Is Nothing Then Exit Do
         Loop While Found.Address <> FirstAddress
         If CountReplaces > 0 Then MsgBox ("You've been replaced!")
         End If
       End With
    TextBox1.Text = ""
    LoadListBox 
</code>[COLOR=#333333]End Sub[/COLOR]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top