检查数组中是否存在值 [英] Checking if Value is Present in an Array

问题描述

我修改了在
中找到的VBA代码检查数组中是否存在值添加到下面的代码。用户将选择一个字段名，并且我希望代码查找列出该字段的列并创建该列中所有项目的数组，但是我希望每个非空白值在该数组中仅显示一次。我不要重复。

I modified the VBA code found at checking if value present in array to the code below. A user will choose a field name and I want the code to find the column that field is listed in and create an array of all items in that column, but I want each non-blank value to show up only once in the array. I don't want any duplicates.

例如，如果该列具有值A，B，A，C，D，则我希望代码将数组返回为A，B，C，D。

For example, if the column has values A, B, A, C, D, I want the code to return the array as A, B, C, D.

我遇到运行时错误13-在此行代码上键入不匹配：

I get a run time error 13 - Type mismatch on this line of the code:

If cell.Value <> "" And IsError(WorksheetFunction.Match(cell.Value, MyArray, 0)) Then

I'我很困惑，因为似乎我所有的数据类型都是正确的。有人可以提供任何帮助吗？

I'm confused, because it seems like all my data types are correct. Can anyone offer any help?

Sub ChangeBlock()

Dim MyArray() As String
Dim cell As Range
Dim ColNum As Integer
Dim i As Integer

If Not Intersect(ActiveCell, Range("Block1")) Is Nothing Then
    If ActiveCell.Value = "" Then Exit Sub

ColNum = WorksheetFunction.Match(ActiveCell.Value, Sheets("Budget Table").Range("A1:AG1"), 0)

    For Each cell In Sheets("Budget Table").Columns(ColNum)
        If cell.Value <> "" And IsError(WorksheetFunction.Match(cell.Value, MyArray, 0)) Then
            ReDim Preserve MyArray(i)
            MyArray(i) = cell.Value
            i = i + 1
        End If
    Next
End If

MsgBox (MyArray)

End Sub

推荐答案

错误13类型不匹配是在这里引起的：

The Error 13 Type mismatch is caused here:

cell.Value<>

这里的 Value 是2D数组，类似于（1至1048576 ，1对1），则无法将此数组与字符串进行比较，因此类型不匹配。

The Value here is 2D-array something like (1 To 1048576, 1 To 1) and it is not possible to compare this array to a string hence the type mismatch.

编辑：

实际上变量 cell 是一列，因此要进行正确比较，必须说要比较数组的哪个元素，例如对于第一个元素：

Actually the variable cell is a column so to compare properly it is necessary to say what element of the array is compared, e.g. for the first element:

cell.Value（）（1，1）=

更正确的方法是将变量 cell 重命名为例如 oneColumn 是因为变量 cell 实际上包含对列的引用，例如像这样：

More correct would be to rename the variable cell to e.g. oneColumn because the variable cell actually contains a reference to a column, e.g. like this:

Dim myColumns As Range
Set myColumns = Sheets("Budget Table").Columns(ColNum)

Dim oneColumn As Range

For Each oneColumn In myColumns
    ' ...
Next oneColumn

注意：此处 for-each 没有意义，因为 myColumns 仅引用一列，而 myColumns.Columns.Count 返回 1 。因此，您实际上想要的只是 myColumns.Cells ，它返回列的所有单元格。

Note: Here the for-each does not make sense because myColumns references just one column and myColumns.Columns.Count returns 1. So all you wanted was actually myColumns.Cells which returns all the cells of the column.

oneColumn 的值是2D数组，老实说，我不知道为什么它是2D而不是1D。当您检查例如的值时 Range（ A1：C3）然后您会看到它返回2D数组，这是可以理解的。但是，为什么一列列也返回2D？似乎对我也很奇怪:)。一维数组的示例为 Dim oneDArray：oneDArray = Array（ A， B， C）。据我所知，一维数组从未从 Range.Value 属性返回。 此处关于VBA中数组尺寸的有趣文章。

The value of oneColumn is 2D-array and to be honest I don't know why it is 2D and not just 1D either. When you would examine Value of e.g. Range("A1:C3") then you see it returns 2D-array which is understandable. But why one column of columns returns 2D as well? Seems to be odd to me as well :). An example of 1D-array would be Dim oneDArray: oneDArray = Array("A", "B", "C"). As far as I know 1D-array is never returned from a Range.Value property. Here interesting article about array dimensions in VBA.

但这不是必需的，因为每个 Range 都有一个属性单元格。因此，此处应使用 Columns（ColNum）.Cells 。

But this is not necessary because each Range has a property Cells. So here the Columns(ColNum).Cells should be used.

完整的代码可能类似于以下内容但这有点太复杂了。首先，由于使用了整个列，因此数组中有很多空元素；其次，我们考虑使用像@ A.S.H这样的字典提出的解决方案。 HTH

The complete code could look something like the following but it is a little bit too complicated. First the array has a lot of empty elements because the whole column is used and second consider the solution with a dictionary like @A.S.H is proposing. HTH

Dim MyArray() As String
Dim cell As Range
Dim ColNum As Integer
Dim i As Integer

ReDim MyArray(0 To 0)

With Sheets("Budget Table")

    If Intersect(ActiveCell, .Range("Block1")) Is Nothing Then _
        Exit Sub

    If ActiveCell.Value = "" Then _
        Exit Sub

    ColNum = Application.Match(ActiveCell.Value, .Range("A1:AG1"), 0)

    For Each cell In .Columns(ColNum).Cells
        If cell.Value = "" Then _
            GoTo continue

        If IsError(Application.Match(cell.Value, MyArray, 0)) Then
            If i > 0 Then
                ReDim Preserve MyArray(i)
            End If

            MyArray(i) = cell.Value
            i = i + 1

        End If
continue:
    Next cell

End With

这篇关于检查数组中是否存在值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！