VBA code to locate identical values---within col A of two worksheets---and replace sheet 1 col A matches with values adjacent i.e., sheet 2 col B

[dougmarkham]

Hi Folks,

I have two systems to harmonize---a logistics system and an accounts management system---which provide different customer numbers: one for invoicing, and one for logistics operations.

My task is to incorporate an 'accounts management' report into a logistics systems spreadsheet to allow bulk update of accounts data into the logistics application; therefore, my tasks involves replacing the 'accounts management'-customer-codes with 'logistics'-customer-codes.

The logistics template workbook we use to bulk update is called "NCR1.xltm".
The 'accounts management' report worksheet is called "Transfer".
The worksheet called CCCT contains table 1 - a table that matches logistics and invoice customer codes.

https://drive.google.com/open?id=1Y86hPxTDZNh1ro4zonPF1y3RIg6Ex9Dl

"Table 1" that has: logistics-customer-codes (in column B) and the corresponding invoice-customer-codes (in column C).

https://drive.google.com/file/d/1WMQVM2wIRyD82ZDjxkyQAr3sgaEbO8Oe/view?usp=sharing

Worksheet "Transfer" contains a table with invoice-customer-codes (in column B).

https://drive.google.com/file/d/15CGXLLmDWkb-JbYebrWeL2kDPPMx7YgH/view?usp=sharing


What I need help generating is VBA code to:

• a) inspect the invoice-customer-codes in column B of the "Transfer" worksheet;
• b) look for matches within Table 1, Column C;
• c) replace the matched invoice-customer-codes---that sit in column B of wksht "Transfer"---with corresponding logistics-customer-codes from Wksht "CCCT" column B.

Just for example, I have created an invoice code for the company Superdrug (SO125) which is populated in Column B of Wksht "Transfer" and whose value is matched in C12 of the Wksht "CCCT".

I need the VBA to replace "SO125" in Column B of Wksht "Transfer" with the logistics-customer-code "027" (located in B12 of the Wksht "CCCT").

I have searched for such a macro and found examples of code that matches and replaces data within two tables in the same worksheet:

Sub Match & Replace
Dim rng as Range
Dim rngInspection as Range
 
Set rng = Range("B2:EX") -- Range of the left table
Set rngInspection = Range("O2:P5") -- Range of the right table
 
For Each rowInspection In rngInspection.Rows
 
   Dim part as string, serial as string, inspectionDate as String
   part = rowInspection.Cells(1).Value
   serial = rowInspection.Cells(2).Value
   inspectionDate = rowInspection.Cells(3)
 
   For Each row in rng.rows
       If (row.Cells(1).Value = part And row.Cells(2).Value = serial) Then
            row.Cells(4).Value = inspectionDate
       EndIf
   Next row
 
Next rowInspection

However, I cannot find any examples of VBA that inspects two columns existing in separate worksheets, finds matches, then copies data from cells in an adjacent column (to that inspected) back into the other worksheet.

Would anybody be willing to share some code they've come across which does this, or help me generate code that does this task?

Kind regards,

Doug.