Looking for better way than .formula = "countif....."

[Bob Zimski]

I managed to get this far with a tip, and it provides the output exactly as I
envisioned it. However, I am looking for a better way to achieve the same
result. What I don't like is that I am populating cells in order to do my
evaluation. It would be preferable if I can do the evaluations without
dumping a formula in a cell.

Thanks in advance for any help.

Bob

Sub InvTest()

' The purpose is to isolate duplicate entries and missing entries
' in summary form from a predetermined sequential range of
' numbers.

Dim firstTag, lastTag, Tag, recCnt, i As Integer
Dim dupPos, misPos As Integer

firstTag = Range("H1")
lastTag = Range("H2")
recCnt = lastTag - firstTag

Tag = firstTag
dupPos = 2
misPos = 2

Range("B1") = "Duplicates"
Range("C1") = "Missing"

For i = 2 To 2 + recCnt
Range("B" & i).Formula = "=CountIf(A:A, " & Tag & ")"

If Range("B" & i) > 1 Then
Range("C" & dupPos) = Tag
dupPos = dupPos + 1
ElseIf Range("B" & i) = 0 Then
Range("D" & misPos) = Tag
misPos = misPos + 1
End If
Tag = Tag + 1
Next i
End Sub

 

[JLGWhiz]

Give this modified For...Next statement a try:

For i = 2 To 2 + recCnt
myVar = WorksheetFunction.CountIf(Range("A:A"), Tag)

If myVar > 1 Then
Range("C" & dupPos) = Tag
dupPos = dupPos + 1
ElseIf myVar = 0 Then
Range("D" & misPos) = Tag
misPos = misPos + 1
End If
Tag = Tag + 1
Next i

 

[Bob Zimski]

Hit the nail right on the head.

Thanks very much.

Bob