blocks|key|3535413|text|这个功能对我很好，如果你需要改进的话，告诉我。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3535414|Public+Function+Compare(r1+As+Range,+r2+As+Range,+r3+As+Range)+As+Long
++++Dim+i
++++Dim+v1
++++Dim+v2
++++Dim+v3
++++Dim+counter

++++counter+=+0
++++For+Each+i+In+r1
++++++++counter+=+counter+%2B+1
++++++++v1+=+r1(counter).Value
++++++++v2+=+r2(counter).Value
++++++++v3+=+r3(counter).Value

++++++++If+v1+=+v2+And+v2+=+v3+Then
++++++++++++'r3(counter).Offset(0,+2).Value+=+"OK"+'this+is+for+the+test
++++++++++++Compare+=+Compare+%2B+1
++++++++++++'I+think+could+be+easy+to+test+and+return+a+value...
++++++++++++'Compare+=+v1+'Because+is+the+same+value+in+the+3+cells
++++++++Else
++++++++++++'r3(counter).Offset(0,+2).Value+=+"NO"'this+is+for+the+test
++++++++++++'Do+another+code...

++++++++End+If
++++Next+i
End+Function|code-block|syntax|javascript|3535415|编辑#1|offset|length|style|BOLD|3535416|这能帮上忙..。|3535417|Public+Function+Compare2(r1+As+Range,+r2+As+Range,+r3+As+Range)+As+Long
++++Dim+i
++++Dim+v1
++++Dim+v2
++++Dim+v3
++++Dim+counter
++++Dim+n1+As+Range
++++Dim+n2+As+Range
++++Dim+n3+As+Range
++++Dim+max

++++counter+=+0
++++max+=+Application.WorksheetFunction.max(r1.Count,+r2.Count,+r3.Count)
++++'With+"max"+take+the+max+number+of+rows+in+the+range+to+use+it

++++Set+n1+=+Range(Cells(r1(1).Row,+r1(1).Column),+Cells(r1(1).Row+%2B+max+-+1,+r1(1).Column))
++++Set+n2+=+Range(Cells(r2(1).Row,+r2(1).Column),+Cells(r2(1).Row+%2B+max+-+1,+r2(1).Column))
++++Set+n3+=+Range(Cells(r3(1).Row,+r3(1).Column),+Cells(r3(1).Row+%2B+max+-+1,+r3(1).Column))
++++'Here+we+set+new+ranges,+equals+all+of+them,+to+use+them+in+the+for+loop+and+compare
++++'we+use+the+ref+of+the+input+ranges.

++++counter+=+0
++++For+Each+i+In+n1
++++++++counter+=+counter+%2B+1+'this+is+the+index+for+the+new+ranges
++++++++v1+=+n1(counter).Value+'store+every+value+of+the+new+ranges
++++++++v2+=+n2(counter).Value
++++++++v3+=+n3(counter).Value

++++++++If+v1+=+v2+And+v2+=+v3+Then+'do+the+comparison,+and+if+the+3+values+are+equal
++++++++++++'n3(counter).Offset(0,+2).Value+=+"OK"+'this+is+just+for+the+test
++++++++++++Compare2+=+Compare2+%2B+1+'add+1+to+compare
++++++++Else
++++++++++++'n3(counter).Offset(0,+2).Value+=+"NO"
++++++++++++'this+part+of+the+code+don't+do+anything
++++++++++++'but+if+you+want+to+put+some+code+is+up+to+you.
++++++++++++'You+can+delete+from+Else+to+this+comment
++++++++End+If
++++Next+i
End+Function|3535418|向函数添加了更多的注释。|3535419|entityMap^0|0|0|0|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

This functions works fine for me, tell me if you need some improvement.

<pre><code>Public Function Compare(r1 As Range, r2 As Range, r3 As Range) As Long
 Dim i
 Dim v1
 Dim v2
 Dim v3
 Dim counter

 counter = 0
 For Each i In r1
 counter = counter + 1
 v1 = r1(counter).Value
 v2 = r2(counter).Value
 v3 = r3(counter).Value

 If v1 = v2 And v2 = v3 Then
 'r3(counter).Offset(0, 2).Value = "OK" 'this is for the test
 Compare = Compare + 1
 'I think could be easy to test and return a value...
 'Compare = v1 'Because is the same value in the 3 cells
 Else
 'r3(counter).Offset(0, 2).Value = "NO"'this is for the test
 'Do another code...

 End If
 Next i
End Function
</code></pre>

Edit #1

This could help... 

<pre><code>Public Function Compare2(r1 As Range, r2 As Range, r3 As Range) As Long
 Dim i
 Dim v1
 Dim v2
 Dim v3
 Dim counter
 Dim n1 As Range
 Dim n2 As Range
 Dim n3 As Range
 Dim max

 counter = 0
 max = Application.WorksheetFunction.max(r1.Count, r2.Count, r3.Count)
 'With "max" take the max number of rows in the range to use it

 Set n1 = Range(Cells(r1(1).Row, r1(1).Column), Cells(r1(1).Row + max - 1, r1(1).Column))
 Set n2 = Range(Cells(r2(1).Row, r2(1).Column), Cells(r2(1).Row + max - 1, r2(1).Column))
 Set n3 = Range(Cells(r3(1).Row, r3(1).Column), Cells(r3(1).Row + max - 1, r3(1).Column))
 'Here we set new ranges, equals all of them, to use them in the for loop and compare
 'we use the ref of the input ranges.

 counter = 0
 For Each i In n1
 counter = counter + 1 'this is the index for the new ranges
 v1 = n1(counter).Value 'store every value of the new ranges
 v2 = n2(counter).Value
 v3 = n3(counter).Value

 If v1 = v2 And v2 = v3 Then 'do the comparison, and if the 3 values are equal
 'n3(counter).Offset(0, 2).Value = "OK" 'this is just for the test
 Compare2 = Compare2 + 1 'add 1 to compare
 Else
 'n3(counter).Offset(0, 2).Value = "NO"
 'this part of the code don't do anything
 'but if you want to put some code is up to you.
 'You can delete from Else to this comment
 End If
 Next i
End Function
</code></pre>

Added more comments to the function.

blocks|key|3535429|text|Public+Function+Compare(r1+As+Range,+r2+As+Range,+r3+As+Range)+As+Long
+++Dim+r+As+Range,+v+As+Variant,+m1+As+Variant,+m2+As+Variant
+++Dim+rv+As+Long

+++rv+=+0
+++For+Each+r+In+r1
++++++v+=+r.Value
++++++If+v+<>+0+And+v+<>+""+And+Not+IsError(v)+Then
++++++++++++m1+=+Application.Match(v,+r2,+0)
++++++++++++m2+=+Application.Match(v,+r3,+0)
++++++++++++If+Not+IsError(m1)+And+Not+IsError(m2)+Then
+++++++++++++++rv+=+rv+%2B+1
++++++++++++End+If
++++++End+If
+++Next+r
+++Compare+=+rv
End+Function|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3535430|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Public Function Compare(r1 As Range, r2 As Range, r3 As Range) As Long
 Dim r As Range, v As Variant, m1 As Variant, m2 As Variant
 Dim rv As Long

 rv = 0
 For Each r In r1
 v = r.Value
 If v &lt;&gt; 0 And v &lt;&gt; "" And Not IsError(v) Then
 m1 = Application.Match(v, r2, 0)
 m2 = Application.Match(v, r3, 0)
 If Not IsError(m1) And Not IsError(m2) Then
 rv = rv + 1
 End If
 End If
 Next r
 Compare = rv
End Function
</code></pre>

blocks|key|3448095|text|以下是非vba解决方案的替代方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3448096|考虑这样的数据布局：|3448097|​|3448098|📷|atomic|offset|length|3448099|3448100|在单元格中，E2是这样的公式：|3448101|=SUMPRODUCT(--(COUNTIF(B2:B16,A2:A23)>0),--(COUNTIF(C2:C19,A2:A23)>0))|code-block|syntax|javascript|3448102|为了清晰起见，我高亮显示了所有三列中都有匹配的单元格。在A列中总共有8个单元格具有B和C列中的重复项，请注意，这将在A列中计算重复值(但您的UDF也是如此)。|3448103|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/641b991a119797e8defdf4e9e2848fae.jpg|imageAlt^0|0|0|0|0|1|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|14|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|15|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|16|8|@]|9|@$I|17|J|18|1|19]]|A|$]]|$1|K|3|E|5|6|7|1A|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1B|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1C|8|@]|9|@]|A|$Q|R]]|$1|S|3|T|5|6|7|1D|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11|12|-4]]]]

Here's an alternative for a non-vba solution.

Consider a data layout like so:

<a href="https://i.stack.imgur.com/FIUnd.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FIUnd.jpg" alt="sample.jpg"></a>

In cell E2 is this formula:

<pre><code>=SUMPRODUCT(--(COUNTIF(B2:B16,A2:A23)&gt;0),--(COUNTIF(C2:C19,A2:A23)&gt;0))
</code></pre>

I have highlighted all of the cells that have matches in all three columns for clarity. There are a total of 8 cells in column A that have duplicates found in columns B and C. Note that this will count duplicates in column A (but so does your UDF already).

<pre><code>Public Function Compare(r1 As Range, r2 As Range) As Long
 Dim r As Range, v As Variant, v2 As Variant
 Dim rr As Range
 For Each r In r1
 v = r.Value
 If v &lt;&gt; 0 And v &lt;&gt; "" Then
 For Each rr In r2
 v2 = rr.Value
 If v = v2 Then Compare = Compare + 1
 Next rr
 End If
 Next r
End Function
</code></pre>

This UDF compares 2 ranges and return the number of matched values. 
I would like to compare 3 ranges instead, in order to find how many values appeared in all 3 ranges simultaneously.

Much appreciate any help.

compare 3 ranges instead of 2

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

Public Function Compare(r1 As Range, r2 As Range) As Long   Dim r As Range, v As Variant, v2 As Variant   Dim rr As Range   For Each r In r1      v = r.Value   ...

问比较3个范围而不是2个范围
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问比较3个范围而不是2个范围EN