PDA

View Full Version : [SOLVED] Compare two sheets



austenr
11-22-2004, 09:53 PM
What do I have to change to make it print the whole line on the recon report not just the cell(s) that are different? I played around with it a couple of ways but had no luck. I know you have to change the .Cells to .Row I think. Thanks guys. You rock!!


Public Sub ReconReport()
Dim rngCell As Range
For Each rngCell In Worksheets("Sheet1").UsedRange
If Not rngCell = Worksheets("Sheet2").Cells(rngCell.Row, rngCell.Column) Then _
Let Worksheets("Sheet3").Cells(rngCell.Row, rngCell.Column) = rngCell
Next
End Sub

austenr
11-24-2004, 10:03 AM
My other post did not get any responses so I am trying again. I want to modify this code to select and write the entire row on Sheet3 instead of just the cells that are changed. Thanks in advance.


Sub UniqueList()
'Build a new unique list from two other lists
'Works by checking list 1 for missing values in list 2
'Sheet1 is the master list of names.
'Sheet2 is the raw data
'Sheet3 gets the names that are in the raw data but not on the master list.
Set MyFunction = Application.WorksheetFunction
Set MyRange1= Sheets("Sheet1").Range("A2:A500")
Set MyRange2 = Sheets("Sheet2").Range("A2:A500")
Set MyResults = Sheets("Sheet3").Range("A2")
?Loop for list values
Sheets("Sheet1").Select
For Each cell In My Range2
On Error GoTo myFin
Sheets("Sheet2").Select
If MyFunction.CountIf(MyRange1, cell.Value) = 0 Then
Sheets("Sheet3").Select
Set MyResults = MyResults.Offset(r, 0)
MyResults.Value = cell.Value
r = r + 1
End If
Next Cell
myFin:
Sheets("Sheet3").Select
Sheets("Sheet3").Range("A1").Select
End Sub

Zack Barresse
11-24-2004, 10:12 AM
Hi Austen, I don't think you need the Let ...


Public Sub ReconReport()
Dim rngCell As Range
For Each rngCell In Worksheets("Sheet1").UsedRange
If Not rngCell.Value = Worksheets("Sheet2").Cells(rngCell.Row, rngCell.Column).Value Then
Worksheets("Sheet3").Cells(rngCell.Row, rngCell.Column) = rngCell.Value
End If
Next
End Sub


Btw, I merged your two threads. There's no need to keep making posts on the same thread. ;) Just give it time.

austenr
11-24-2004, 11:33 AM
will either of these print the entire row if it finds a discrepancy? Or could they be modified to do so? Thanks

Zack Barresse
11-24-2004, 11:37 AM
Can you post a zipped example spreadsheet?

austenr
11-24-2004, 01:23 PM
Here is the example file. As you can see there are rows with no change, rows with one or two differences and one record that totally does not match the other. I want to print the entire row of Sheet1 if there are any changes in any cell or if there is a new row on Sheet1 that does not appear on Sheet2. I wanted to write the rows to Sheet3. Hope this helps explain it better.

Zack Barresse
11-24-2004, 04:36 PM
So in your example file, which rows would be copied to sheet 3? You do want the entire row copied over if there is a discrepancy, right? And then you want to print out that sheet (sheet3)?

austenr
11-24-2004, 04:55 PM
Right. Also if there is one (an entire row on the raw data that does not match any thing on the master). Right now it would write the raw data because it has no corresponding data that matches. But if the raw data and master differ even by one cell I would want to write the entire row. HTH

Zack Barresse
11-24-2004, 06:10 PM
Okay, here is a routine. I am a little confused as to which sheet is the 'Master' and which is the 'raw data' though..


Option Explicit

Sub CheckValuesAndMoveEm()
Dim rngCel As Range, rngChk As Range, tmpCel As Range, _
tmpRng As Range, myRow As Long, myCol As Long, cel As Range
Set rngChk = Sheets("Sheet1").Range("A1", Sheets("Sheet1"). _
Range("A65536").End(xlUp))
For Each rngCel In rngChk
With Sheets("Sheet2")
Set tmpCel = .Range("A:A").Find(rngCel.Value)
If Not tmpCel Is Nothing Then
Set tmpRng = Range(tmpCel, .Cells(tmpCel.Row, 256). _
End(xlToLeft))
For Each cel In tmpRng
If cel.Value <> Sheets("Sheet1").Cells(cel.Row, _
cel.Column).Value Then
rngCel.EntireRow.Copy Sheets("Sheet3"). _
Range("A65536").End(xlUp).Offset(1)
GoTo nextCellPlease
End If
Next cel
Else
'** NOT FOUND **
End If
End With
nextCellPlease:
Next rngCel
End Sub


This will search sheet2 for differences in sheet1, and put them all in sheet3. Is that what you wanted or do I have it backwards?

austenr
11-24-2004, 08:50 PM
hey thanks..Sheet1 is the raw data Sheet2 is the master...

austenr
11-25-2004, 01:53 PM
Hey that works copying the entire row, however, if you look at the example file I am attaching there is an extra row that does not get written to Sheet3. I have been looking at it and tried a few things testing wise but can't figure out why it does not write the row. Any ideas?

Zack Barresse
11-25-2004, 02:32 PM
Because I didn't put anything in there if not found. You see the commented code in the Else statement

'** NOT FOUND **
Replace that with another statement like this ...

rngCel.EntireRow.Copy Sheets("Sheet3"). _
Range("A65536").End(xlUp).Offset(1)
That should get you the rows not found in the second sheet.

austenr
11-25-2004, 07:01 PM
Works great. Thanks. Someone please take a look at what I tried to modify this to highlight different colors. Compiles o.k. but I get an error that the "Object does not support this" with respect to the highlighting I am trying to apply. Thanks.