检查数组中是否存在值 [英] checking if value present in array

问题描述

我正在使用这个问题，但是，在我的情况下似乎不起作用。

I'm using a function from this question, however, it doesn't seem to work in my case.

基本上，这个脚本正在通过一列选择不同的值并填充数组 arr 与他们。首先如果正在检查列是否已经结束，那么为了避免调用空数组，我有第一个 IfElse ，最后我想检查单元格字符串的非空数组。如果不存在，我想添加它。

Basically, this script is going through a column selecting distinct values and populating array arr with them. First If is checking if the column has ended, then to avoid calling empty array I have the first IfElse, and finally I want to check a non-empty array for cell string. If it is not present, I want to add it.

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Sub SelectDistinct()

    Dim arr() As String
    Dim i As Integer
    Dim cells As Range

    Set cells = Worksheets("types").Columns("A").Cells

    i = 0
    For Each cell In cells
        If IsEmpty(cell) Then
            Exit For
        ElseIf i = 0 Then
            ReDim Preserve arr(i)
            arr(UBound(arr)) = cell
            i = i + 1
        ElseIf IsInArray(cell.Value, arr) = False Then
            ReDim Preserve arr(i)
            arr(UBound(arr)) = cell
            i = i + 1
        End If
    Next cell
End Sub

由于某种原因，它会在<$ c $的调用中引发下标超出范围错误c> IsInArray 函数。有人可以让我知道我出错了吗？

For some reason, it throws "Subscript out of range" error on the call of IsInArray function. Can someone let me know where I went wrong?

推荐答案

这是我将如何做一维数组，使用 Application.Match 函数，而不是另一个UDF。

Here is how I would do it for a one-dimensional array, using the Application.Match function, instead of another UDF.

我已经整合了一些If / ElseIf逻辑a Do ... While 循环，然后使用 Match 函数来检查单元格值是否存在于数组中。如果不存在，则将其添加到数组中并继续到范围中的下一个单元格。

I have consolidated some of your If/ElseIf logic with a Do...While loop, and then use the Match function to check whether cell value exists in the array. If it does not exist, then add it to the array and continue to the next cell in your range.

Sub SelectDistinct()

Dim arr() As String
Dim i As Integer
Dim cells As Range
Dim cl As Range
Dim foundCl As Boolean

    Set cells = Worksheets("Sheet6").Columns(1).cells

    Set cl = cells.cells(1)

    Do
        If IsError(Application.Match(cl.Value, arr, False)) Then
            ReDim Preserve arr(i)
            arr(i) = cl
            i = i + 1
        Else:
            'Comment out the next line to completely ignore duplicates'
            MsgBox cl.Value & " already exists!"

        End If

        Set cl = cl.Offset(1, 0)
    Loop While Not IsEmpty(cl.Value)

End Sub

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