Replace 2 values from 2 different tables at once

[KnewIT]

Hi,
It's my first post here.

I have 2 tables in Excel that come from VBA data extraction from some other software.

I’d like for the common values in the NAME column on each table to be linked, meaning : if, for instance, I replaced ABC02 in the second table with ABC03 then the first table’s ABC02 would be replaced with ABC03 as well.

and conversely (a change in the first table would lead to a change in the second one).

I've tried using the Handle value to mark identical values with the following code:

Sub Test1()
Dim i, y As Integer

For i = 10 To 11
    y = 7
    Do Until y = 5
        y = y - 1
        If Range("C" & y).Value = Range("C" & i).Value Then
            Range("D" & y).Value = Range("B" & i).Value
        End If
    Loop

'' Action :

Next i

End Sub

Which gave this result:

Test.xlsm
	A	B	C	D
3	File1.dwg
4	Blockname	Handle	NAME
5	Block1	386	ABC02	27F
6	Block2	2F7	ABC01	291
7	Block3	267
8	File2.dwg
9	Blockname	Handle	NAME
10	Block1	291	ABC01
11	Block2	27F	ABC02
TEST

But I don't know how to efficiently do it both ways (meaning getting the handles of similar values for the other table as well) to get that result:

Test.xlsm
	A	B	C	D
3	File1.dwg
4	Blockname	Handle	NAME
5	Block1	386	ABC02	27F
6	Block2	2F7	ABC01	291
7	Block3	267
8	File2.dwg
9	Blockname	Handle	NAME
10	Block1	291	ABC01	2F7
11	Block2	27F	ABC02	386
Feuil6

and how to go from there (or even if I should do that at all ?)

I tried to be as clear as possible by including visuals but if there's anything I can clearify further, don't hesitate to tell me.

Thanks for reading and for any input.

 

[Joe4]

Welcome to the Board!

Why not just use Find/Replace?
For example, if you want to change "ABC02" to "ABC03", simply find and replace all instances of "ABC02" with "ABC03" (Excel's "Replace All" feature will do them all for you at once).

If you really want VBA, you could do this via VBA, where you prompt the user to enter the original value, then prompt them for what they want to change it to, and then have VBA do the Find/Replace All.

 

[KnewIT]

Welcome to the Board!

Why not just use Find/Replace?
For example, if you want to change "ABC02" to "ABC03", simply find and replace all instances of "ABC02" with "ABC03" (Excel's "Replace All" feature will do them all for you at once).

If you really want VBA, you could do this via VBA, where you prompt the user to enter the original value, then prompt them for what they want to change it to, and then have VBA do the Find/Replace All.

Thanks!

Actually, I have to use VBA and can't prompt the user for input.
Someone from another forum posted the following answer (so I'm posting it here in case anyone is looking for the same thing):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Variant
Application.EnableEvents = False
Set f2 = Sheets("Image")
If Not Intersect(Target, Columns(3)) Is Nothing Then
    If Range(Target.Address).Value <> f2.Range(Target.Address).Value Then
        TargetImage = f2.Range(Target.Address).Value
        With f2
            Set x = Columns(3).Find(TargetImage)
            If Not x Is Nothing Then
                Pos = x.Address
                Do
                    If x.Row <> Target.Row Then
                        Cells(x.Row, "C") = Target.Value
                        'Modification of the Image sheet
                        f2.Cells(x.Row, "C") = Target.Value
                        f2.Range(Target.Address).Value = Target.Value
                    Else
                        Set x = .FindNext(x)
                    End If
                Loop While Not x Is Nothing And x.Address <> Pos
            End If
        End With
    End If
End If
Set f2 = Nothing
Application.EnableEvents = True
End Sub

This code assumes that:

1. There are 2 identical sheets and the second one is called "Image"
2. The user input is in the first sheet
3. The values we want to replace are both in the 3rd column
4. There are no duplicate values within the same table

If you want to execute another macro beforehand (like I do with my data extraction) you have to use that bit of code within that macro

    On Error GoTo ErrHandler
    Application.EnableEvents = False

'Your code here...

ErrHandler:
   Application.EnableEvents = True

If somehow you modify that Worksheet_Change macro and get an error, you have to fix that error and enable events again with another macro like this one:

Sub ReenableEventsAfterError()
Application.EnableEvents = True
End Sub

 

[Joe4]

Someone from another forum posted the following answer (so I'm posting it here in case anyone is looking for the same thing):

Thank you for posting back.

If posting on other forums (known as "Cross-Posting"), please note our policy on that. While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Most other forums also have the same policy (or do not allow it altogether).

 

[KnewIT]

Thank you for posting back.

If posting on other forums (known as "Cross-Posting"), please note our policy on that. While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Most other forums also have the same policy (or do not allow it altogether).

OK, thanks and sorry. ?

I will include the link to the other forum but I'm still unaware of their rules on cross-posting (I haven't seen anything about it in their rules).
That forum is French and insists on using French only.

 

[Joe4]

I will include the link to the other forum but I'm still unaware of their rules on cross-posting (I haven't seen anything about it in their rules).
That forum is French and insists on using French only.

OK. I was thinking most of the other big international Excel help forums. Most have similar policies.
Basically, it is important to know that the question has been asked elsewhere, so people do not waste time working up solutions that may have already been suggested elsewhere, or worse yet, they may waste time working on something that has already been solved (no one likes to feel that their time and efforts are wasted).

 

[KnewIT]

The other forum where I got help

 

[Joe4]

Thank you.