vba 在具有重复值的数组中查找值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18600523/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding Values in an array with duplicates values
提问by ExcelMacroStudent
I have an array of customer names. This array is full of duplicates, and needs to be since other data in the order row may vary. There are no unique identifiers for this data and I need to compare the data from one order against all the rows who have that customer in the array.
我有一系列客户名称。该数组充满了重复项，并且必须如此，因为订单行中的其他数据可能会有所不同。此数据没有唯一标识符，我需要将一个订单中的数据与数组中具有该客户的所有行进行比较。
I'm having trouble getting the for loop to search all the rows that have a customer match.
我无法使用 for 循环来搜索所有具有客户匹配项的行。
Any help would be appreciated!
任何帮助，将不胜感激！
Dim prodlog As String
Dim orddate As Variant
Dim cus As String
Dim owner As String
Dim orddate2 As Variant
Dim owner2 As String
Dim LogCusts As Variant
LogCusts = Application.Transpose(Range("F3", Range("F" & Rows.count).End(xlUp)))
Dim loglen As Integer
loglen = UBound(LogCusts) - LBound(LogCusts)
Dim cust2 As Variant
For Each cust2 In LogCusts
Dim custrow As Integer
custrow = Application.Match(cust2, LogCusts, False) + 1
prodlog = Range(Cells(custrow, 5), Cells(custrow, 5)).Value
orddate = Range(Cells(custrow, 2), Cells(custrow, 2)).Value
cus = Range(Cells(custrow, 6), Cells(custrow, 6)).Value
owner = Range(Cells(custrow, 7), Cells(custrow, 7)).Value
databook.Activate
logjam.Select
orddate2 = Range(Cells(custrow + 1, 5), Cells(custrow + 1, 5)).Value
owner2 = Range(Cells(custrow + 1, 7), Cells(custrow + 1, 7)).Value
If IsEmpty(orddate) Then
Exit For
End If
If IsEmpty(prodlog) Then
trackbook.Activate
masterlog.Select
Range(Cells(custrow, 2), Cells(custrow, 17)).Clear
Else: While cus = cust2
If orddate = orddate2 And owner = owner2 Then
Range(Cells(custrow, 8), Cells(custrow, 8)).Value = prodlog
End If
Wend
End If
Next cust2
回答by lfrandom
There are a couple of ways to do what you want to do. The easiest is probably to use a dictionary like @Richard Morgan suggests in his comment to you.
有几种方法可以做你想做的事。最简单的方法可能是使用@Richard Morgan 在他对您的评论中建议的字典。
You can also loop through the array and create a new array with just the correct names.
您还可以遍历数组并使用正确的名称创建一个新数组。
Sample Code:
示例代码：
Private Sub UniqueArray(oldData as Variant) as Collection
Set UniqueArray = New Collection
Dim i as integer
Dim j as integer
Dim foundFlag as boolean
For i = 1 to oldData.Count
foundFlag = False
FOr j = i + 1 to oldData.Count
If oldData(i) = oldData(j) then
foundFlag = True
End If
Next j
If not foundFlag then UniqueArray.Add oldData(i)
Next
Again, I think using a dictionary is probably better, but this should work.
同样，我认为使用字典可能更好，但这应该有效。
回答by user3040865
Wow that looks so complicated, have you tried creating a table next to your current array and use the formula:
哇，看起来这么复杂，您是否尝试过在当前数组旁边创建一个表并使用以下公式：
=IF(MAX(COUNTIF(A2:A11,A2:A11))>1,"Duplicates","No Duplicates")
that will display Duplicatesif there is a duplicate and display No Duplicatesif there is non. from A2-A11 of course.
Duplicates如果有重复将显示，如果没有则显示No Duplicates。当然是从A2-A11。
Or to keep things really simple you can use conditional formatting, type in something like
或者为了让事情变得非常简单，您可以使用条件格式，输入类似
=COUNTIF($B:$B,B2)=1
for items for values that only appear once. you can slightly modify to get a color scheme for your array. Then throw in a Key on the side that tells which color means how many duplicates.
对于仅出现一次的值的项目。您可以稍微修改以获得阵列的配色方案。然后在旁边扔一个钥匙，告诉哪个颜色表示有多少重复。
Don't know if that helps, but I try to keep away from VBA when ever I can.
不知道这是否有帮助，但我尽量远离 VBA。
