Funky Sheet Compare....Can't Make Macro Work???

[mbarnett]

Hi,

I found the below code in one of the threads and was wondering if can be amended to handle the following:

I have two sheets I want to compare and spit out discrepancies in another sheet (called discrepancies). The Id will be common each sheet. I want to compare amt, order and price (see sample below). If any of the ids have diffs, I only want to see those id with diffs appear in my discrepancy sheet.

Id amt order price
C79389 0.201 126 97.5625
829223 0.246 154 97.546875
G08112 9.965 3893 99.96875

Sub FindDiscrepancies()
Dim RowCount As Long, ColCount As Integer, lRow As Long, iCol As Integer
Dim Frm1, Frm2, Discrepancy(), idx
'--activate first workbook/sheet and assign row & col counts
' (assuming starting in row 1 col 1
Sheet2.Activate
With Cells(1, 1).CurrentRegion
RowCount = .Rows.Count
ColCount = .Columns.Count
End With

idx = 1
For lRow = 1 To RowCount
For iCol = 1 To ColCount
'switch to wkbk/sht 1
Sheet2.Activate
Frm1 = Cells(lRow, iCol).Formula
'switch to wkbk/sht 2
Sheet3.Activate
Frm2 = Cells(lRow, iCol).Formula
'compare formulae
If Frm1 <> Frm2 Then
ReDim Preserve Discrepancy(1 To 2, 1 To idx)
Discrepancy(1, idx) = lRow
Discrepancy(2, idx) = iCol
idx = idx + 1
End If
Next
Next
'activate the report sheet
Sheet4.Activate
For lRow = LBound(Discrepancy, 2) To UBound(Discrepancy, 2)
For iCol = LBound(Discrepancy, 1) To UBound(Discrepancy, 1)
Cells(lRow, iCol).Value = Discrepancy(iCol, lRow)
Next
Next
End Sub