Count Find Array

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.

Code:

---

x = [Count(Find({2,4}, 12345))]

---

Hello MD
Here's my try

Code:

---

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

---

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

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:

Code:

---

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.

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'.

Code:

---

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

---

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

…but why avoid looping?

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.

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

Quote:

---

Originally Posted by 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.

---

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.

Quote:

---

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.

Raining in Scotland ????

Quote:

---

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)