VBA finding if value is in values

vba check if value is in array
vba instr
vba find value in array
vba isinarray function not defined
excel-vba check if value exists in list
excel-vba check if value exists in range
vba fastest way to search array
vba check if item in list

I have a value y, and I want to find out if that value is inside this set of values : x1, x2, ... xn.

I can do it like this:

if(y = x1 or y = x2 or .....)

But is there a nicer way? pseudocode:

if(y in (x1, x2, ...., xn))

You could write a helper function like this one:

Public Function FindValue(ByVal ValueToFind As Variant, ParamArray SearchIn() As Variant)

    Dim i As Integer

    For i = 0 To UBound(SearchIn)
        If SearchIn(i) = ValueToFind Then
            FindValue = True
            Exit Function
        End If
    Next

End Function

The second parameter (the ParamArray) is an array, so you can actually pass an indefinite number of parameters.

So you can just pass all your values to this function - the one you want to find first, and after that all the ones you want to search in:

Dim Found As Boolean

Found = FindValue(y, x1, x2, x3, xn)

Check if Value is in Array using VBA, Check if a value is in an array with this VBA function. 'INPUT: Pass the function a value to search for and an array of values of any data type. Return value. A Range object that represents the first cell where that information is found. Remarks. This method returns Nothing if no match is found. The Find method does not affect the selection or the active cell. The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify

You can use Select Case in the same manner of (if then else):

Select Case Y
       Case X1, X2, X3, ...
            Do if True
       Case Else
            Do if False
End Select

Using Isnumeric and Isnumber in VBA, This article will teach you how to use the IsNumeric and IsNumber functions in VBA to check if values are numbers. IsNumeric is a built-in VBA function, while  Excel VBA Find – How to find any value in a range of cells with VBA. Many times as a developer you might need to find a match to a particular value in a range or sheet, and this is often done using a loop. However, VBA provides a much more efficient way of accomplishing this using the Find method.

Use arrays:

dim x(10)

x(1)=....
x(2)=....

y=....

for i=1 to 10
    if x(i)=y then
         ....
    end if
next i

Check If Value Is In List in Excel, So in this article, we will learn how to check if a values is in a list or not using various ways. Check If Value In Range Using COUNTIF Function. So as we know​,  Find value in Range, Sheet or Sheets with VBA. Copy the code in a Standard module of your workbook, if you just started with VBA see this page. Where do I paste the code that I find on the internet. Find is a very powerful option in Excel and is very useful. Together with the Offset function you can also change cells around the found cell.

dim values as string
values = "1,2,3,4,5,"  'Note that comma is a separator and added towards the end as well.

dim lookupValue as string
lookupValue = ",4,"

dim found as Boolean
found = (instr(1, values, lookupValue) <> 0)

if found then


end if

EDIT: Another way

dim lookupValue as long
lookupValue = 21000

dim values
set values = CreateObject("Scripting.Dictionary")

with values
   .Add 1, 1
   .Add 10, 1
   .Add 100, 1
   .Add 1000, 1
   .Add 10000, 1
end with

dim found as Boolean
found = values.Exists(lookupValue)

Excel VBA FIND Function (& how to handle if value NOT found , VB Copy. With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues​) If Not c Is Nothing Then firstAddress = c.Address Do c.Value  It returns a single dimension variant array with just two values, the two indices of the array used as an input (assuming the value is found). If the value is not found, it returns an array of (-1, -1) .

Quite easy idea to check if the value exist is to use Match function:

Dim myTBL As Variant
    myTBL = Array(20, 30, 40, 50, 60)

'if value '30' exists in array than the position (which is >0) will be returned
    Debug.Print WorksheetFunction.Match(30, myTBL, 0)

The only problem is that if the value doesn't exist the Match function returns an error. Therefore you should use error handling technique.

That could look like for non existing value '70':

'if doesn't exists error would be returned
On Error Resume Next
    Debug.Print WorksheetFunction.Match(70, myTBL, 0)
If Err.Number <> 0 Then
    Debug.Print "not exists"
    Err.Clear
End If

Unfortunately, that will work only in Excel.

Range.FindNext method (Excel), Excel VBA Find – How to find any value in a range of cells with VBA LookIn: Look in formulas, values or notes using constants xlFormulas, xlValues, And an error will be thrown if you try to perform any operation on this (on  Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more Excel VBA check if value is not in range

Excel VBA Find, If you want to just let the VBA run and see the values in the range printed each cell in the range and print its value to the Immediate window. Parameter Type Description Values; What: Required: The value you are searching for: Any VBA data type e.g String, Long: After: Optional: A single cell range that you start your search from

Checking Values in Range Objects With VBA • My Online Training Hub, Supposing, I have two columns of data which contains some duplicate values in these both columns as left screenshot shown. And now, I want to find if the  An expression is nothing but the value we are testing and the value could be a cell reference, direct value, or variable assigned value as well. The Null indicates that expression or variable doesn’t contain valid data. Null is not the empty value because VBA thinks variable value has not yet been started and doesn’t treat as Null.

How to check or find if value exists in another column?, If your dates are values xlValues is not always working with some date formats. Sub Find_Todays_Date() Dim FindString As Date Dim Rng As Range FindString =  VBA find duplicate values in a column Excel Macros Examples Codes: to find all duplicate records in a column in MS Excel 2003, 2007, 2010, 2013. We will also see the practical VBA example for finding the duplicates in a Column.