Looking for better way than .formula = "countif....."
Hit the nail right on the head.
Thanks very much.
Bob
"JLGWhiz" wrote:
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" wrote in message
...
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
|