Adding date variable to a search and copy/paste VBA code

[PA_VA13]

Hi everyone,

I was able to create a Button (Assign Cases To SP) that would run a module with a VBA code to find matching case numbers in column A from each worksheet and then transfer the data from column D from worksheet "Assign" to column D in worksheet "SP".

I cannot figure out how to add a cross reference date check to this code to prevent data procured on a different date for the same accession number in worksheet "SP" from being overwritten. I need the code to check the date (ex: 5/24) in column C in worksheet "Assign" to column C in worksheet "SP". If the dates match, then I want the code to continue with the copy and pasting. If the dates do not match, then I want the code to leave those cells alone. Worksheet "SP" will often have multiple lines for one accession number with a different date, depending on when the procedure was performed for that part of the case.

I cannot use the L2BB feature as it is blocked on my work PC, but I did include screen captures of the excel sheet.

This is the code that I came up with so far.

Button Code:

Private Sub CommandButton1_Click()

Call asd

End Sub

Module Code:

Sub asd()

Dim lngLastRowSht1 As Long
Dim lngLastRowSht2 As Long
Dim counterSht1 As Long
Dim counterSht2 As Long

With Worksheets("Assign")
lngLastRowSht1 = .Cells(.Rows.Count, 1).End(xlUp).Row
lngLastRowSht2 = Worksheets("SP").Cells(Worksheets("SP").Rows.Count, 1).End(xlUp).Row
For counterSht1 = 1 To lngLastRowSht1
For counterSht2 = 1 To lngLastRowSht2
If .Cells(counterSht1, 1) = Worksheets("SP").Cells(counterSht2, 1) Then
Worksheets("SP").Cells(counterSht2, 4) = .Cells(counterSht1, 4)
End If
Next counterSht2
Next counterSht1
End With

End Sub

 

[PA_VA13]

Hi vcoolio,

I worked perfectly. Thank you!

PA_VA13

 

[vcoolio]

You're welcome PA_VA13. I'm glad to have been able to help again.

Cheerio,
vcoolio.

 

[PA_VA13]

Morning everyone,

I need some help. This VBA code that vcoolio helped me with worked beautifully all year in 2022, but for some reason it will not recognize dates for 2023. All of the data is located in the exact same format and cells (date cells are all in the same "Date" format; i.e. 12/30/2022 & 1/43/2023) for both sheets. The only difference is now I have dates ending in 2023. I did a test and if the date ends in 2022 the code performs like it should. I did not received any error messages. The code goes through all of the steps, but does not paste anything into sheet 2 if the date ends in 2023.


Private Sub CommandButton1_Click()

Dim lr As Long, lr2 As Long
Dim sValue As Range, c As Range

Application.ScreenUpdating = False

lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
lr2 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
Sheet2.Range("D2:D" & lr2).ClearContents

For Each c In Sheet1.Range("A2:A" & lr)
Set sValue = Sheet2.Columns("A:A").Find(c.Value)
If sValue Is Nothing Then GoTo Nextc
If c.Value = sValue.Value And c.Offset(, 2).Value = sValue.Offset(, 2) Then
c.Offset(, 3).Copy sValue.Offset(, 3)
End If
Nextc:
Next c

Sheet2.Select
Application.ScreenUpdating = True

End Sub