Find and replace cells using VLOOKUP MATCH in VBA from one workbook to another

[JBM91]

Hi experts,

I'm fairly new to VBA to say the least, and I have looked far and wide without finding a solution to my problem.
I have posted questions here in the past, where I have received amazing tips, tricks and answers from you guys - so I figured I'd try again!

Much like the title of the thread suggest, I was wondering if it would in any way be possible to create a macro that will find and replace the cell values in one workbook utilizing something like the vlookup match function, so that it will only target cells where it gets an exact "hit" based on several parameters, and then replace said values with the corresponding cell value from my reference workbook and leave other cells as they are. It would then have to perform this action for all values in my workbook in all visible columns. It's also important to note that it will have to copy potential fill color of cells!

E.g.

So the workbook that will be changed will look as follows

Would something like that even be possible?

Any help, tips and/or tricks would be much appreciated - thanks in advance!

 

[kennypete]

There are several ways to attack this but I think the most robust is to (a) create a dictionary of the "WillHaveValuesChanged" sheet's Data items, (b) compare it to the "Reference" sheet's items/interior colours and update them where Data X's descriptions have changed, and then (c) apply the updated dictionary values back in the "WillHaveValuesChanged" sheet.

The following achieves that, noting I used one Workbook with two sheets, but changing it to be two distinct workbooks would not be difficult.

My Sheets:

and

1. Module and Class Module as shown:

2. Class module:

' Data Class Module Code
Public cm_strDescription As String
Public cm_strFurtherDescription As String
Public cm_dblFurtherDescriptionInterior As Double

3. Module:

Sub subUpdateDescriptions()

    Dim varCell As Variant
    Dim strInterior As String
    Dim dicData As Object
    ' Create the dictionary - late binding
    Set dicData = CreateObject("Scripting.Dictionary")
    
    ' Create dictionary from the used range
    rngUsed = Worksheets("WillHaveValuesChanged").UsedRange
    For i = LBound(rngUsed) To UBound(rngUsed)
        varCell = rngUsed(i, 1)
        If Len(varCell) > 0 Then
            ' Create a new Data object
            Dim objData As Object
            Set objData = New clsData
            ' Set the values
            objData.cm_strDescription = rngUsed(i, 2)
            objData.cm_strFurtherDescription = rngUsed(i, 3)
            objData.cm_dblFurtherDescriptionInterior = Worksheets("WillHaveValuesChanged").UsedRange.Rows(i).Columns(3).Interior.Color
            ' Add the object to the dictionary with its key
            dicData.Add rngUsed(i, 1), objData
        End If
    Next i

    ' Now look for new descriptions in the Reference sheet and replace them in the dictionary
    rngUsed = Worksheets("Reference").UsedRange
    For i = LBound(rngUsed) To UBound(rngUsed)
        varCell = rngUsed(i, 1)
        If Len(varCell) > 0 Then
            If dicData.Exists(varCell) = True Then
                If dicData(varCell).cm_strDescription <> rngUsed(i, 2) Then
                    dicData(varCell).cm_strDescription = rngUsed(i, 2)
                End If
                If dicData(varCell).cm_strFurtherDescription <> rngUsed(i, 3) Then
                    dicData(varCell).cm_strFurtherDescription = rngUsed(i, 3)
                    ' Replace interior colour too
                    dicData(varCell).cm_dblFurtherDescriptionInterior = Worksheets("Reference").UsedRange.Rows(i).Columns(3).Interior.Color
                End If
            End If
        End If
    Next i

    ' Now replace and reformat the original WillHAveValuesChanged sheet's values/interior colours
    ' with the updated values in the dictionary
    rngUsed = Worksheets("WillHaveValuesChanged").UsedRange
    For i = LBound(rngUsed) To UBound(rngUsed)
        varCell = rngUsed(i, 1)
        If Len(varCell) > 0 Then
            If rngUsed(i, 2) <> dicData(varCell).cm_strDescription Then
                Worksheets("WillHaveValuesChanged").UsedRange.Rows(i).Columns(2).Value = dicData(varCell).cm_strDescription
            End If
            If rngUsed(i, 3) <> dicData(varCell).cm_strFurtherDescription Then
                Worksheets("WillHaveValuesChanged").UsedRange.Rows(i).Columns(3).Value = dicData(varCell).cm_strFurtherDescription
            End If
            If Worksheets("WillHaveValuesChanged").UsedRange.Rows(i).Columns(3).Interior.Color <> dicData(varCell).cm_dblFurtherDescriptionInterior Then
                Worksheets("WillHaveValuesChanged").UsedRange.Rows(i).Columns(3).Interior.Color = dicData(varCell).cm_dblFurtherDescriptionInterior
            End If
        End If
    Next i
    
End Sub

====

Run subUpdateDescriptions and the result is as wanted ...