Worksheet change event to edit data range using lookuplist update

[devofish]

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?

 

[mart37]

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.

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

 

[devofish]

I'm sorry, that worked - as in no errors - but it is not updating the values. Below is the total update routine.

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

 

[mart37]

I miss the code with OldValue.

[COLOR=#333333]            OldValue = .Cells(RowCurrent, miCOL_NO__GROUP).Value
[/COLOR][COLOR=#333333]            .Cells(RowCurrent, miCOL_NO__GROUP).Value = Me.TextBox1.Text
[/COLOR]

 

[devofish]

Thankyou. It works perfectly mart37.

 

[devofish]

Here is the final 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]