View Full Version : [SOLVED:] Count Find Array
mdmackillop
07-20-2017, 07:18 PM
This will return the number of found items (2). Is there a syntax that will allow me to use variables instead of numbers, or do I just use a workaround.
x = [Count(Find({2,4}, 12345))]
YasserKhalil
07-20-2017, 11:55 PM
Hello MD
Here's my try
Sub Test()
Dim arr As Variant
Dim s As String
Dim i As Long
Dim x As Long
Dim c As Long
s = "12345"
arr = [{2,4,8}]
For i = LBound(arr) To UBound(arr)
x = CharCount(s, CStr(arr(i)))
If x = 1 Then c = c + 1
Next i
MsgBox c
End Sub
Function CharCount(str As String, chr As String) As Integer
CharCount = Len(str) - Len(Replace(str, chr, ""))
End Function
mdmackillop
07-21-2017, 02:37 AM
Hi Yasser
That's the workaround I'll probably end up using, but is there a way to avoid the loop?
Regards
MD
p45cal
07-21-2017, 02:45 AM
perhaps:
x = Evaluate("Count(Find({" & a & "," & b & "}, " & c & "))")
I don't know which numbers you want to replace with variables so I've done all of them:
Sub fff()
x = [Count(Find({2,4}, 54321))]
y = Evaluate("Count(Find({2,4}, 54321))")
a = 2
b = 4
c = 54321
Z = Evaluate("Count(Find({" & a & "," & b & "}, " & c & "))")
End Sub
but you realise this won't 'return the number of found items'; it will max to 2 and return how many of {2,4} exist in 12345 . So even if 2 exists 3 times it will only count the first of them.
mdmackillop
07-21-2017, 02:50 AM
Thanks Pascal
That is exactly what I was after. I thought I had tried every permutation of Evaluate, but obviously not. The max Count is not an issue in this case.
Regards
MD
Any string will do in 'Evaluate'.
Sub M_snb()
c00 = "{2, 4}"
y = Evaluate("count(find(" & c00 & ",12345))")
End Sub
mdmackillop
07-21-2017, 04:32 AM
Thanks all. Finally got a decent solution to that "matching duplicates" question.
p45cal
07-21-2017, 05:29 AM
…but why avoid looping?
mdmackillop
07-21-2017, 05:52 AM
The question involved 371k cells. A non-looping solution seemed preferable and simpler. CharCount method where single/double digits are involved would get complicated leaving a Match solution.
YasserKhalil
07-21-2017, 06:53 AM
I am so glad of these solutions. You're all of you awesome excel experts
p45cal
07-21-2017, 07:44 AM
The question involved 371k cells. A non-looping solution seemed preferable and simpler. CharCount method where single/double digits are involved would get complicated leaving a Match solution.
It's just that Evaluate is slow; I'm not sure where the 371k cells would appear in your formula, but testing here shows that it's about 50 times slower than looping, but I may not be testing the right thing.
I considered it a ridiculous task. He doesn't explain the purpose of it all.
mdmackillop
07-21-2017, 10:37 AM
I considered it a ridiculous task. He doesn't explain the purpose of it all.
I totally agree, but it was a quiet day and raining outside.
mdmackillop
07-21-2017, 12:46 PM
Raining in Scotland ????
Hard to believe, and I live on the dry side.
Pascal
I tested a loop, maybe not the most elegant, It was 37% faster on 27k cells (55 seconds total)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.