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

Question

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.

Loop intill you find the desired value, then refer to the cell you want to change with that rownumber — Rasmus Damgaard Nielsen
– Rasmus Damgaard Nielsen, Commented Aug 24, 2015 at 15:07
Just to clarify, you want to update the column E for the inspection date of Q where the set Part/Serial of O/P match the Part/Serial of B/C? — Jaime Mendes
– Jaime Mendes, Commented Aug 24, 2015 at 15:10
@jaimetotal, that is correct. I wasn't sure if there was a built in method to do it rather then a loop. Thank you. — jordan lyman
– jordan lyman, Commented Aug 24, 2015 at 15:41
@jordanlyman Check the answer. If it helps you, mark it as an answer :) — Jaime Mendes
– Jaime Mendes, Commented Aug 24, 2015 at 15:57

Jaime Mendes · Accepted Answer · 2015-08-24 16:29:53Z

1

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

edited Aug 24, 2015 at 16:29

answered Aug 24, 2015 at 15:16

Jaime Mendes

6433 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jordan lyman Over a year ago

Its not giving any errors but it isn't affecting the excel sheet how it should. Does rowInspection need to be declared as something else? I changed EX to E10 and O2:P5 to O2:Q2 (its just one row). Any suggestions

jordan lyman Over a year ago

Dim rng As Range Dim rngInspection As Range Set rng = Range("B2:E10") ' Range of the left table Set rngInspection = Range("O2:Q2") 'Range of the right table For Each rowInspection In rngInspection.Rows Dim part As String, serial As String, inspectionDate As String part = rowInspection.Cells(0).Value serial = rowInspection.Cells(1).Value inspectionDate = rowInspection.Cells(2).Value For Each Row In rng.Rows If (Row.Cells(0).Value = part And Row.Cells(1).Value = serial) Then Row.Cells(3).Value = inspectionDate End If Next Row Next rowInspection

Jaime Mendes Over a year ago

Sorry, I had to review it. Had some issues (VBA don't start the arrays at 0). Can you confirm?

jordan lyman Over a year ago

That's perfect. Thank you very much.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related