dougmarkham
Active Member
- Joined
- Jul 19, 2016
- Messages
- 252
- Office Version
- 365
- Platform
- Windows
Hi Folks,
I am looking for a way using VBA to do the following process:
I have two worksheets: Sheet 1 (contains Table 1) and Sheet 2 (contains table 2).
Worksheet 1:
Worksheet 2:
Goal: I would like to run a macro which compares all the serial numbers in WkSht1 Table 1 - Col A with the index serial numbers in WkSht2 - Table 2 - Col A; once complete, I would like the VBA to copy the corresponding alpha numeric codes from WkSht2 - Col B and paste them over the serial numbers in WkSht1 - Col A.
For example:
Step 1: VBA would search Col A in Table 1, identifying them...
Worksheet 1 - Table 1
<tbody>
</tbody>
Step 2: VBA would match the identified serial numbers with those indexed in Table 2 - Col A:
Worksheet 2 - Table 2
<tbody>
</tbody>
Then copy and paste the alpha-numeric codes back into Table 1 Column A (over-writing the serial codes).
Worksheet 1 - Table 1
<tbody>
</tbody>
I have some code that matches and replaces data within two tables in the same worksheet; however, I would like some help modifying the code to do as outlined.
I am looking for a way using VBA to do the following process:
I have two worksheets: Sheet 1 (contains Table 1) and Sheet 2 (contains table 2).
Worksheet 1:
- Table 1 - Column A contains serial numbers.
Worksheet 2:
- Table 2 - Column A contains an index of all the serial numbers.
- Table 2 - Column B contains corresponding alpha numeric codes (one for each serial number).
Goal: I would like to run a macro which compares all the serial numbers in WkSht1 Table 1 - Col A with the index serial numbers in WkSht2 - Table 2 - Col A; once complete, I would like the VBA to copy the corresponding alpha numeric codes from WkSht2 - Col B and paste them over the serial numbers in WkSht1 - Col A.
For example:
Step 1: VBA would search Col A in Table 1, identifying them...
Worksheet 1 - Table 1
Column A | Column B |
123457 | 01/06/2018 |
123459 | 01/06/2018 |
<tbody>
</tbody>
Step 2: VBA would match the identified serial numbers with those indexed in Table 2 - Col A:
Worksheet 2 - Table 2
Column A | Column B |
123456 | ABC1 |
123457 | ABC2 |
123458 | ABC3 |
123459 | ABC4 |
123460 | ABC5 |
<tbody>
</tbody>
Then copy and paste the alpha-numeric codes back into Table 1 Column A (over-writing the serial codes).
Worksheet 1 - Table 1
Column A | Date |
ABC2 | 01/06/2018 |
ABC4 | 01/06/2018 |
<tbody>
</tbody>
I have some code that matches and replaces data within two tables in the same worksheet; however, I would like some help modifying the code to do as outlined.
Code:
Code:
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