RileyMartin
05-09-2018, 01:35 PM
Hi, I'm new to Excel VBA and struggling with syntax and the correct functions to use. I've tried different approaches but keep running into errors. Listed below is my latest attempt.
What I am trying to do is to compare a list from one sheet to the data on another sheet, row by row, cell by cell, and when a match is made, copy that entire row to a target sheet, break out of the for loop that compares the value against the cells in the current row, and goto the next row and start the cell by cell comparison again. Any help would be greatly appreciated. Thank you.
Option Explicit 'Added by SamT
Sub CopyRowsWithMatchingData()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i, D, R, C 'Aded by SamT To satisfy Option Explicit
Set ws1 = Worksheets("DATA")
Set ws2 = Worksheets("LIST")
For D = 2 To 100 '(This is the loop to go through my list of values to compare)
For R = 1 To 4091 '(This will be the row reference for the rows on the DATA sheet to loop through)
For C = 1 To 2471 '(This will be the column reference for the cells in the current row to compare against)
If ws2.Cells("A", D).Value = ws1.Cells(R, C).Value Then '(Compare value from LIST sheet against the current cell in the current row on the DATA sheet)
ActiveWorkbook.Sheets("DATA").Rows(R).EntireRow.Copy
ActiveWorkbook.Sheets("TARGET").Activate
ActiveWorkbook.Sheets("TARGET").Rows(i).EntireRow.Select
ActiveWorkbook.Sheets("TARGET").Paste
Exit For '(At the very first match I copied the entire row to the TARGET sheet and then exit the loop and stop compare cells in that column)
End If
Next C
Next R
Next D
End Sub
What I am trying to do is to compare a list from one sheet to the data on another sheet, row by row, cell by cell, and when a match is made, copy that entire row to a target sheet, break out of the for loop that compares the value against the cells in the current row, and goto the next row and start the cell by cell comparison again. Any help would be greatly appreciated. Thank you.
Option Explicit 'Added by SamT
Sub CopyRowsWithMatchingData()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i, D, R, C 'Aded by SamT To satisfy Option Explicit
Set ws1 = Worksheets("DATA")
Set ws2 = Worksheets("LIST")
For D = 2 To 100 '(This is the loop to go through my list of values to compare)
For R = 1 To 4091 '(This will be the row reference for the rows on the DATA sheet to loop through)
For C = 1 To 2471 '(This will be the column reference for the cells in the current row to compare against)
If ws2.Cells("A", D).Value = ws1.Cells(R, C).Value Then '(Compare value from LIST sheet against the current cell in the current row on the DATA sheet)
ActiveWorkbook.Sheets("DATA").Rows(R).EntireRow.Copy
ActiveWorkbook.Sheets("TARGET").Activate
ActiveWorkbook.Sheets("TARGET").Rows(i).EntireRow.Select
ActiveWorkbook.Sheets("TARGET").Paste
Exit For '(At the very first match I copied the entire row to the TARGET sheet and then exit the loop and stop compare cells in that column)
End If
Next C
Next R
Next D
End Sub