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))]
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))]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
Hi Yasser
That's the workaround I'll probably end up using, but is there a way to avoid the loop?
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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:
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.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
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Any string will do in 'Evaluate'.
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
…but why avoid looping?
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I am so glad of these solutions. You're all of you awesome excel experts
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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.I considered it a ridiculous task. He doesn't explain the purpose of it all.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Raining in Scotland ????
Hard to believe, and I live on the dry side.Raining in Scotland ????
Pascal
I tested a loop, maybe not the most elegant, It was 37% faster on 27k cells (55 seconds total)
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'