检查 VBA 列中是否存在值 [英] Check if value exists in column in VBA

问题描述

我有一列超过 500 行的数字.我需要使用 VBA 来检查变量 X 是否与列中的任何值匹配.

I have a column of numbers of over 500 rows. I need to use VBA to check if variable X matches any of the values in the column.

有人可以帮我吗?

推荐答案

如果你想不用 VBA，你可以使用IF、的组合>ISERROR 和 MATCH.

If you want to do this without VBA, you can use a combination of IF, ISERROR, and MATCH.

因此，如果所有值都在 A 列中，请在 B 列中输入此公式:

So if all values are in column A, enter this formula in column B:

=IF(ISERROR(MATCH(12345,A:A,0)),"Not Found","Value found on row " & MATCH(12345,A:A,0))

这将查找值12345"(也可以是单元格引用).如果未找到该值，MATCH 将返回#N/A"，ISERROR 会尝试捕获该值.

This will look for the value "12345" (which can also be a cell reference). If the value isn't found, MATCH returns "#N/A" and ISERROR tries to catch that.

如果你想使用 VBA，最快的方法是使用 FOR 循环:

If you want to use VBA, the quickest way is to use a FOR loop:

Sub FindMatchingValue()
    Dim i as Integer, intValueToFind as integer
    intValueToFind = 12345
    For i = 1 to 500    ' Revise the 500 to include all of your values
        If Cells(i,1).Value = intValueToFind then 
            MsgBox("Found value on row " & i)
            Exit Sub
        End If
    Next i

    ' This MsgBox will only show if the loop completes with no success
    MsgBox("Value not found in the range!")  
End Sub

您可以在 VBA 中使用工作表函数，但它们很挑剔，有时会抛出无意义的错误.FOR 循环非常万无一失.

You can use Worksheet Functions in VBA, but they're picky and sometimes throw nonsensical errors. The FOR loop is pretty foolproof.

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