Looking for better way than .formula = "countif....."
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 |
Looking for better way than .formula = "countif....."
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 |
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 |
All times are GMT +1. The time now is 02:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com