If all cells match, copy down the date VBA

Lenna

Active Member
Joined
Jun 25, 2014
Messages
269
I have to fill in report dates for tests. Each patient has multiple tests but “Report date” is only recoded for one of them. I’ve sorted the spreadsheet so that the row that contains the report date is first and all other tests in the same grouping are to follow.

I have a Single sheet that contains thousands of rows of continues data(no blank rows).

I need to match the content of each cell with a row below (columns A through I), to make sure that it’s the same grouping of test that was reported at the same time.
Only if all cells match( A through I), copy down the “Report date”. Repeat this until the end of data.

Please suggest some code.

IDMRNLastNameFirstNameCategorySampleDateLogTimeAssignedDateTestedDateTestTypeTestIDReportDate
11111222222222SmithBobLung1/24/20141/24/20141/24/20141/25/2014S_A11111/26/2014
11111222222222SmithBobLung1/24/20141/24/20141/24/20141/25/2014S_B1112
11111222222222SmithBobLung1/24/20141/24/20141/24/20141/25/2014S_C1113
11111222222222SmithBobLung1/24/20141/24/20141/24/20141/25/2014S_D1114
11111222222222SmithBobLung1/24/20141/24/20141/24/20141/26/2014S_E1114

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Thanks
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Make your cell L3 the activecell. With L3 active highlight L3:L6. in Cell L3 enter the "=" sign then with the Up Arrow point to cell L2. then Hold down the Control-Key and simultaneoulsly press the Enter Key. This should replicate a formula into all the highlighted cells. You can then Again select them all again (L3:L6) and Copy and Paste-Special Values to change the formulas to values.
 
Upvote 0
Would it work if I record it for a macro? I would prefer VBA code...I'm interested in learning what is actually happening...

Only columns A through F have to match…my apologies

Thank you very much,

Lenna
 
Last edited:
Upvote 0
Try... UNTESTED
Code:
Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr + 1
    If Range("A" & r).Value = Range("A" & r + 1).Value And _
        Range("B" & r).Value = Range("B" & r + 1).Value And _
        Range("C" & r).Value = Range("C" & r + 1).Value And _
        Range("D" & r).Value = Range("D" & r + 1).Value And _
        Range("E" & r).Value = Range("E" & r + 1).Value And _
        Range("F" & r).Value = Range("F" & r + 1).Value Then
        Range("L" & r + 1).Value = Range("L" & r).Value
    End If
    Next r
End Sub
 
Upvote 0
beautiful and simple
Thank you very much!

Lenna
Try... UNTESTED
Code:
Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr + 1
    If Range("A" & r).Value = Range("A" & r + 1).Value And _
        Range("B" & r).Value = Range("B" & r + 1).Value And _
        Range("C" & r).Value = Range("C" & r + 1).Value And _
        Range("D" & r).Value = Range("D" & r + 1).Value And _
        Range("E" & r).Value = Range("E" & r + 1).Value And _
        Range("F" & r).Value = Range("F" & r + 1).Value Then
        Range("L" & r + 1).Value = Range("L" & r).Value
    End If
    Next r
End Sub
 
Upvote 0
Glad it worked.....I'm sure there is a better way, but I didn't have Excel to play with it....:beerchug;
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top