How to check if value of cell is a part of an array

[bukimi]

Hello,

I need some help again. I'm working with thousands of lines of data in Excel and I need to check if any value in a specified column is one of the values from a specified set of values.
I was reading a bit about arrays and thought that they may be useful here, but I never used them before and I'm not sure how to get it to work right.


I'm running a check for all rows (from 2 to my last row declared earlier) and don't know how to do a check for arrays (it's BelongsToArray now in code)

Dim MyArray
MyArray= Array(8182, 8183)
Dim OtherArray
OtherArray= Array(19909, 20201, 20317)


For i = 2 To lastrow
        If .Cells(i,10).value [B][I]BelongsToMyArray [/I][/B]or .Cells(i,12).value [B][I]BelongsToOtherArray [/I][/B]Then
        -do stuff-
        End If
    Next i

Is there a way to do a similar check, using arrays or not. I don't want to introduce too many "or" in single If statement for all numbers in my arrays or go through all rows more than once (as it would be slow).

Thank you in advance for all your tips!

 

[nemmi69]

This might work

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Public[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit] IsInArray[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]String2BeFound [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Inarray [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Boolean[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] LBound[/FONT][/COLOR][COLOR=#303336][FONT=inherit](Inarray)[FONT=inherit] [/FONT][COLOR=#101094][FONT=inherit]To[/FONT][/COLOR][FONT=inherit] UBound[/FONT][FONT=inherit](Inarray)[/FONT]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit]        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Inarray([FONT=inherit]i[/FONT][FONT=inherit])[/FONT][FONT=inherit] [/FONT][FONT=inherit]=[/FONT][FONT=inherit] String2BeFound [/FONT][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit]            IsInArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Exit[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i
    IsInArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
 [/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function
[/FONT][/COLOR]

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Inarray being the array to check
</code>String2BeFound is the string to search for

 

[bukimi]

I need two functions, one for each array, right?

 

[dmt32]

Hi,
to find an element in an array you can use Match


something like following may do what you want

Sub InArrayTest()
    Dim BelongsToMyArray As Variant, m As Variant
    Dim BelongsToOtherArray As Variant, m1 As Variant
    
    BelongsToMyArray = Array(19909, 20201, 20317)
    BelongsToOtherArray = Array(8182, 8183)
    
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastrow
        m = Application.Match(Cells(i, 10).Value, BelongsToMyArray, 0)
        m1 = Application.Match(Cells(i, 12).Value, BelongsToOtherArray, 0)
        If Not IsError(m) Or Not IsError(m1) Then
    
        'do stuff
        End If
    Next i
End Sub

If either of the tested cell values match then no error is returned.

Dave

 

[bukimi]

Works fine. Match function does miracles!
Thank you very much!

 

[dmt32]

Works fine. Match function does miracles!
Thank you very much!

welcome - glad suggestion helped

Dave