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

PA_VA13

New Member
Joined
Jul 2, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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
 

Attachments

  • Worksheet Assign.png
    Worksheet Assign.png
    46.4 KB · Views: 4
  • Worksheet SP.png
    Worksheet SP.png
    52 KB · Views: 5

PA_VA13

New Member
Joined
Jul 2, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
Hi vcoolio,

I worked perfectly. Thank you!

PA_VA13
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,203
Office Version
  1. 365
Platform
  1. Windows
You're welcome PA_VA13. I'm glad to have been able to help again.

Cheerio,
vcoolio.
 

Forum statistics

Threads
1,148,020
Messages
5,744,364
Members
423,864
Latest member
GolfingTitan116

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
Top