Help! Looking a VBA that checks 2 cells and copies some data to another cell if the criteria is met.

WhiteRaven76

New Member
Joined
Jun 27, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I have been trying out some different code, but have hit a dead end.

I am trying to find a way to have a VBA check column AG for a date and check column D on the same row for a number (010, 012, 013 or 014). If both of those are true, it will paste the date from column AG to a specific cell and paste the time from column AB to a specific cell otherwise it will continue checking and cross checking cells AG and D until it reaches the last row.

Any help would be greatly appreciated. I have been struggling with this for the last week.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

portews

Active Member
Joined
Sep 4, 2009
Messages
434
Office Version
  1. 2016
  2. 2003 or older
Platform
  1. Windows
Try this:

VBA Code:
Sub CheckMatch()
'https://www.mrexcel.com/board/threads/help-looking-a-vba-that-checks-2-cells-and-copies-some-data-to-another-cell-if-the-criteria-is-met.1209548/
Dim LastRow As Integer
Dim CopyDate As Integer
Dim CopyTime As Integer
Dim i As Integer

'finds the last row used.  Change the Sheet name as needed and column one that will always have data
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

CopyDate = 6 'change this to the number of the column you want to paste the date into
CopyTime = 7 'change this to the number of the column you want to paste the time into

'change  2 to where you want to start your comparison
For i = 2 To LastRow

'see if it's in a valid date format.
If IsDate(Cells(i, 33)) = True Then
    'look at the D column
    Select Case Cells(i, 4)
    'if it equals 10,12, 13, 14 then (assuming your cell is formatted for numbers not text)
    'if it is formatted for text change to "010", "012", "013", "014"
    Case 10, 12, 13, 14
        'copy the date
        Cells(i, CopyDate) = Cells(i, 33)
        'copy the time
        Cells(i, CopyTime) = Cells(i, 28)
    End Select
    End If
'next row
Next

End Sub
 

WhiteRaven76

New Member
Joined
Jun 27, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Try this:

VBA Code:
Sub CheckMatch()
'https://www.mrexcel.com/board/threads/help-looking-a-vba-that-checks-2-cells-and-copies-some-data-to-another-cell-if-the-criteria-is-met.1209548/
Dim LastRow As Integer
Dim CopyDate As Integer
Dim CopyTime As Integer
Dim i As Integer

'finds the last row used.  Change the Sheet name as needed and column one that will always have data
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

CopyDate = 6 'change this to the number of the column you want to paste the date into
CopyTime = 7 'change this to the number of the column you want to paste the time into

'change  2 to where you want to start your comparison
For i = 2 To LastRow

'see if it's in a valid date format.
If IsDate(Cells(i, 33)) = True Then
    'look at the D column
    Select Case Cells(i, 4)
    'if it equals 10,12, 13, 14 then (assuming your cell is formatted for numbers not text)
    'if it is formatted for text change to "010", "012", "013", "014"
    Case 10, 12, 13, 14
        'copy the date
        Cells(i, CopyDate) = Cells(i, 33)
        'copy the time
        Cells(i, CopyTime) = Cells(i, 28)
    End Select
    End If
'next row
Next

End Sub
Hi porters,

Thanks for the assist! This is partially working.

A great leap forward from what I was able to do myself though!

It is checking the row and verifying if it has the correct info in column 4 and (if it does) copies over the data from columns 28 and 33. It is copying it into column 1 and 2 of the same row however.

Is it possible for it to copy the date and time (which it verified was associated with 010 in column 4) into cell A41 (date) and B41 (time). Continue to the next row and copy the date and time for the next row into A42/B42 if the 012 exists, otherwise skip to copying the date/time to A43/B43 and skip if it doesn’t exist and finally copying the date/time for 014 into A43 and B43.

The following days will be in ranges F41/G41 though F44/G44. I attached a photo of what I was hoping to get the final product to look like.
 

Attachments

  • C04787DF-6201-4FE4-BE6E-9AE59A7B579D.png
    C04787DF-6201-4FE4-BE6E-9AE59A7B579D.png
    19 KB · Views: 2

WhiteRaven76

New Member
Joined
Jun 27, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Of course, auto correct was extremely determined to fix your name from portews to porters 🙄
 

Forum statistics

Threads
1,181,757
Messages
5,931,869
Members
436,805
Latest member
waseem abbas

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