Anyone knows whats wrong with the below codes??

Sub Compare2()
Dim wb As Workbook
Dim wsResult As Worksheet
Dim wsLut As Worksheet
Dim rngCrit1 As Range
Dim rngCrit2 As Range
Dim rngSelected As Integer
Dim rngToCount As Range
Dim rngCritNew As Range
Application.DisplayAlerts = False

On Error Resume Next

Set wb = ActiveWorkbook
If rngCrit1 < "" And rngCrit2 < "" Then
Set rngCrit1 = Application.InputBox("Select data range: "
Type:=8)
Set rngCrit2 = Application.InputBox("Select criteria range: "
Type:=8)
wb.Sheets("Results").Delete
wb.Sheets.Add After:=Sheets("Data")
ActiveSheet.Name = "Results"
Set wsLut = ActiveWorkbook.Sheets("LUT")
Set wsResult = ActiveWorkbook.Sheets("Results")
rngCrit1.Copy Destination:=wsResult.Range("A2")
rngCrit2.Copy Destination:=wsResult.Range("C2")
wsResult.Range("A1") = "Data"
wsResult.Range("A1").Font.Bold = True
wsResult.Range("C1") = "Criterias"
wsResult.Range("C1").Font.Bold = True
wsResult.Range("D1") = "Result of Count"
wsResult.Range("D1").Font.Bold = True
Else
MsgBox "Please select the criteria"
End If

rngSelected = rngCrit1.Count
If rngSelected < 0 Then
With wsResult
Set rngCritNew = .Range("C2", .Range("C2").End(xlDown))
For Each c In rngCritNew
Crit = c.value
critCnt
Application.WorksheetFunction.SumProduct(Len(rngCr it1)
Len(Application.WorksheetFunction.Substitute(rngCr it1, "Crit", "")))
Len("Crit")
c.Offset(0, 1).value = critCnt
Next

ttlmatched
Application.WorksheetFunction.Sum(rngCritNew.Offse t(0, 1))
.Range("C2").End(xlDown).Offset(1, 0) = "No. Matched"
.Range("C2").End(xlDown).Offset(0, 1) = ttlmatched
.Range("C2").End(xlDown).Offset(1, 0).value = "No. NO
matched"
.Range("C2").End(xlDown).Offset(0, 1).value = rngSelected
ttlmatched
End With
MsgBox "Count completed"
End If
Application.DisplayAlerts = True
Exit Sub
End Sub


i believe tat my problem lies he

For Each c In rngCritNew
Crit = c.value
critCnt
Application.WorksheetFunction.SumProduct(Len(rngCr it1)
Len(Application.WorksheetFunction.Substitute(rngCr it1, "Crit", "")))
Len("Crit")
c.Offset(0, 1).value = critCnt
Next

as the codes run smoothly but doesn;t display the result. Anyone ca
help?

--
Message posted from http://www.ExcelForum.com