CODEWITHSUNDEEP
excelvbareplacefind
jordan lyman
jordan lyman

Reputation: 13

VBA: Searching for a value, replace value in different column but same row

In Column B I have part numbers, in Column C I have serial Numbers, and in column E I have the most recent inspection date of each part/serial combination. I need the inspection date to be updated to a new date that is referenced in another cell. I tried to add an image but it will not let me. I will try to recreate the excel image below:

Example:

  B                            C                D               E

Part Number___________Serial Number________________Inspection Date

75750-0001-0000___________ 0002 ______________________9/15/2014

Update Information:

  O                            P                     Q

Part Number___________Serial Number_______Inspection Date

75750-0001-0000___________0004_______________8/24/2015

In O5 there is have a part number, in P5 I have a serial number, and Q5 I have the inspection date of that part/serial. I need to click a button and have the inspection date update the correct part/serial number in the list. (The actual list is much larger so doing it manually would be very time consuming). There are no duplicates or anything like that. Any help would be greatly appreciated.

Upvotes: 1

Views: 2628

Answers (1)

Jaime Mendes
Jaime Mendes

Reputation: 643

The idea here is to make a double loop. Search for the correct row and update the date.

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

Upvotes: 1

Related Questions