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

[WhiteRaven76]

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.

 

[portews]

Try this:

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]

Try this:

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.

 

[WhiteRaven76]

Of course, auto correct was extremely determined to fix your name from portews to porters