c4rn3v1l3
Posts Tuesday June 13, 2017Registration date
June 21, 2017
Last seen
-
Jun 13, 2017 at 08:03 PM - Latest reply: vcoolio1159
Posts Thursday July 24, 2014Registration dateModeratorStatus
March 28, 2018
Last seen
- Jun 22, 2017 at 03:59 AM
I really need help figuring out this issue that I am not too sure that actually has a solution.
I have two separate spreadsheets (lets call them "sheet1" and "sheet2") both of which in column "A" have IP addresses listed.
What I need to do is if "sheet1" has a matching IP in "sheet2" i want to have a copy of the entire matching row from "sheet1" be put into a new sheet (sheet3).
Essentially If 'A' from sheet1 = 'A' from sheet2 then copy the matching row of information into sheet3 (sheet3 will become its own spreadsheet consisting of all the matches in IP from the other two sheets)
Try the following code, placed in a standard module and assigned to a button:-
Sub CompareSheets()
Application.ScreenUpdating = False
Dim cell As Range
Dim sValue As Range
For Each cell In Sheet1.Range("A2", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp))
Set sValue = Sheet2.Columns("A:A").Find(cell.Value)
If sValue Is Nothing Then GoTo NextCell
If cell.Value = sValue.Value Then
Sheet1.Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
End If
NextCell:
Next cell
Sheet3.Select
Application.ScreenUpdating = True
End Sub
The code should find matching values in Column A of both sheets 1 and 2 and then transfer the relevant row of data from sheet1 to sheet 3.
Run the code from Sheet 1.
I hope that this helps.
Cheerio,
vcoolio.
Was this answer helpful?Thanks for your feedback.
Don't forget to take a few seconds to say thanks: Thanks for your feedback.
We're sorry. Our aim is to give you the best possible answer.
vcoolio1159
Posts Thursday July 24, 2014Registration dateModeratorStatus
March 28, 2018
Last seen
>
c4rn3v1l3
Posts Tuesday June 13, 2017Registration date
June 21, 2017
Last seen -Jun 15, 2017 at 10:05 PM
Well unfortunately I have run into a problem.I tried to reuse the same code after making a slight tweak, buy making it post the the differences in the two sheets to a 3rd sheet. So Im getting a run time error "424" object required
Sub CompareSheets()
Application.ScreenUpdating = False
Dim cell As Range Dim sValue As Range
For Each cell In Sheet1.Range("A2", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp)) Set sValue = Sheet2.Columns("A:A").Find(cell.Value) If sValue Is Nothing Then GoTo NextCell If cell.Value <> sValue.Value Then Sheet1.Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2) End If NextCell: Next cell Sheet3.Select
Application.ScreenUpdating = True
End Sub
"Sheet1.Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)" is where im getting the error
To check for differences in two sheets, we may have to change tack a little and reference one sheet from the other and vice versa. So try the following code ( untested ):-
Sub CompareSheets2()
Application.ScreenUpdating = False
Dim lr As Long
Dim lCol As Long
Dim i As Integer
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
lCol = Sheet1.Cells(2, Columns.Count).End(xlToLeft).Column
Sheet1.Select
For i = 2 To lr
If Sheet2.Cells(i, 1).Value <> Sheet1.Cells(i, 1).Value Then
Sheet1.Range(Cells(i, 1), Cells(i, lCol)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
End If
Next
Sheet2.Select
For i = 2 To lr
If Sheet1.Cells(i, 1).Value <> Sheet2.Cells(i, 1).Value Then
Sheet2.Range(Cells(i, 1), Cells(i, lCol)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
End If
Next
Sheet3.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Again, the code assumes that you are looking for differences in Column A only. If you need to check for differences over entire rows, then let me know.
Cheerio,
vcoolio.
"Sheet1.Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)" is where im getting the error