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]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SearchRange As Range
If Intersect(Target, ActiveSheet.Columns(1)) Is Nothing Then Exit Sub
Application.EnableEvents = False
NewValue = Target
Application.Undo
OldValue = Target
Target = NewValue
Set SearchRange = Sheets(1).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
        x = 0
        Do
            Sheets(1).Range(Found.Address) = NewValue
            x = x + 1
            Set Found = .FindNext(Found)
            If Found Is Nothing Then GoTo DoneFinding
        Loop While Found.Address <> firstAddress
DoneFinding:
        If x > 0 Then MsgBox (x & " Items replaced!")
    End If
End With
Application.EnableEvents = True
End Sub

 

[devofish]

Thanks for the reply. It's a great start. Unfortunately I get a variable not defined error on NewValue. If I dim it as range then it errors on OldValue; then on Found, FirstAddress, etc. If I dim everything, nothing happens.

 

[mart37]

Dim NewValue as Variant

 

[devofish]

I tried that, too. I tried diming OldValue as variant as well. It just keeps giving errors down the line as in once a variable is defined, it errors on the next undefined declaration.

 

[mart37]

Do yoy have something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewValue As Variant
Dim OldValue As Variant
Dim SearchRange As Range
Dim Found As Variant
Dim FirstAddress As Variant
Dim CountReplaces As Integer
If Intersect(Target, ActiveSheet.Columns(1)) Is Nothing Then Exit Sub
If IsEmpty(Target) Then
    MsgBox "Cell is empty!"
    Exit Sub
End If
Application.EnableEvents = False
NewValue = Target
Application.Undo
OldValue = Target
Target = NewValue
Set SearchRange = Sheets(1).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
            Sheets(1).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
Application.EnableEvents = True
End Sub

 

[devofish]

I was close, but the revised works flawlessly. Slick! Thank you very much!

 

[devofish]

The only thing is it doesn't seem to work if the update (change) is made from a userform while I'm another sheet. My userform has a list box, text box and update button to update the values on my sheet. My lookuplist is hidden from the user. I've incorp a couple WithEndWith into the search functions but an error occurs on

Application.Undo

when the workbook is opened the first time. Once the debugger is stopped, the worksheet change code is no longer operating (I'm assuming bc the application is no longer active). Would this be something that I could incorp into my command button?

 

[devofish]

Can anyone help me with this?

I ran the code below and it works to update the host sheet without error:

[COLOR=#333333]Private Sub cmdUpdate_Click()[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">    
    Dim ws1 As Worksheet
    Set ws1 = ActiveSheet
    Dim ws2 As Worksheet
    Set ws2 = Worksheets(msSHEET_NAME)

    Application.ScreenUpdating = False
    'msg box code
    ws2.Activate
    'update code
    ws1.Activate
    Application.ScreenUpdating = True
    'more code </code>[COLOR=#333333]    End Sub[/COLOR]

The value on the host sheet changed via the macro. Why is the worksheet change event not recognizing the value change even when the macro is run while the host sheet is active? It works when I change the value on my keyboard, but not through the form.​

 

[mart37]

I am missing a mine reply on an early post. (Much trouble on this website!)
When you use a form than you can control your input in the form.
You need no worksheet_change or other event.