At the moment the code looks like this:
all the duplicates are written out to DUPLICATION_SHEET and am only checking
sheets whose name starts with 'Plan'.
Please correct the below code....
URGENT!!!! pretty please guys
Private Sub CheckDuplicates(ByVal strWB As Workbook, ByVal sDesc As String,
ByVal sStartCol As String, ByVal sEndCol As String)
Application.ScreenUpdating = False
Dim response As String
Dim strMsg As String
Dim intLastDataRowToCheck As Integer
Dim nRow As Integer
Dim nColumn As Integer
Dim nSourceRow As Integer
Dim bDuplicate As Boolean
Dim sFields() As String
Dim strLabel As String
Dim strOutputRec As String
Dim strParent As String
Dim strDesc As String
Dim iParent
Dim ws As Worksheet
intLastDataRowToCheck =
CInt(Worksheets(SHEET_CONTROL).Range(sRangeMaxRows).Value)
nRow = 2
strMsg = "Click OK to run Duplicate Checks for " & sDesc
response = MsgBox(strMsg, vbOKCancel, "Run Duplicate Checks for " & sDesc)
If response = vbOK Then
Dim nCount As Integer
Dim dFlat As Object
Dim dHier As Object
Set dFlat = CreateObject("Scripting.Dictionary")
Set dHier = CreateObject("Scripting.Dictionary")
dFlat.CompareMode = vbTextCompare
dHier.CompareMode = vbTextCompare
Sheets(DUPLICATION_SHEET).Cells.Delete shift:=xlUp
'Check only Plan worksheets
For Each ws In Worksheets
Select Case ws.Name
Case "Plan Duplicates"
Case "Control"
Case "HierarchyView"
Case "Validations"
Case "Account"
Case "Entity"
Case "Custom1"
Case "Custom2"
Case "Custom3"
Case "Custom4"
Case "AppSettings"
Case Else
For nCount = intStartDataRow To intLastDataRowToCheck
'Check only rows with Extract = "Y"
If Trim(ws.Range("E" & nCount)) = "Y" Then
strParent = Trim(CStr(ws.Cells(nCount, 2).Value))
strLabel = Trim(CStr(ws.Cells(nCount, 3).Value))
strDesc = Trim(CStr(ws.Cells(nCount, 4).Value))
strOutputRec = ws.Name & DELIM_ATTR & CStr(nCount) &
DELIM_ATTR & sStartCol & DELIM_ATTR & sEndCol
If strLabel <> "" Then
If Not dFlat.Exists(strLabel) Then
dFlat.Add strLabel, strOutputRec
Else
sFields = Split(dFlat(strLabel), DELIM_ATTR)
nSourceRow = CInt(sFields(COL_SOURCE_ROW))
'Check for Duplicates across worksheets
bDuplicate = False
For nColumn = ws.Range(sStartCol &
nCount).Column To ws.Range(sEndCol & nCount).Column
If ws.Cells(nSourceRow, nColumn) <>
ws.Cells(nCount, nColumn) Then
bDuplicate = True
End If
Next
If bDuplicate Then
Sheets(DUPLICATION_SHEET).Range("A" &
nRow) = "Rows " & nSourceRow & ", " & nCount & " are shared nodes with
conflicting attributes in " & ws.Name & ""
Call formatValRow(DUPLICATION_SHEET, nRow)
nRow = nRow + 1
ws.Rows(nSourceRow & ":" &
nSourceRow).Copy
Sheets(DUPLICATION_SHEET).Rows(nRow &
":" & nRow).Insert shift:=xlDown
nRow = nRow + 1
ws.Rows(nCount & ":" & nCount).Copy
Sheets(DUPLICATION_SHEET).Rows(nRow &
":" & nRow).Insert shift:=xlDown
nRow = nRow + 1
End If
End If
End If
End If
Next nCount
End Select
Next ws
'Cleanup
Set dHier = Nothing
Set dFlat = Nothing
Sheets(DUPLICATION_SHEET).Select
MsgBox "Checking Duplicates Completed"
Else
MsgBox "Duplicate Checks Process Cancelled"
End If
End Sub