Count Find Array

[mdmackillop]

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]

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]

Hi Yasser
That's the workaround I'll probably end up using, but is there a way to avoid the loop?
Regards
MD

[p45cal]

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]

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

[snb]

Any string will do in 'Evaluate'.


Sub M_snb()
c00 = "{2, 4}"
y = Evaluate("count(find(" & c00 & ",12345))")
End Sub

[mdmackillop]

Thanks all. Finally got a decent solution to that "matching duplicates" question.

[p45cal]

…but why avoid looping?

[mdmackillop]

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]

I am so glad of these solutions. You're all of you awesome excel experts

[p45cal]

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.

[snb]

I considered it a ridiculous task. He doesn't explain the purpose of it all.

[mdmackillop]

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.

[snb]

Raining in Scotland ????

[mdmackillop]

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)