Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to write formula for countif (range,"<2" &"3") | Excel Discussion (Misc queries) | |||
Difficulty "countif"-ing number of "L1" or "L2" on an autofiltered | Excel Worksheet Functions | |||
how does this formula work: =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D41) | Excel Discussion (Misc queries) | |||
can I nest COUNTIF functions(C5:C8,"apples") AND (D5:D8,"green") | Excel Worksheet Functions | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming |