use array variable instead of multiple Or

[neveu]

hi forum,

i would need to create a loop to check if a cell value is one of the following values: AB, AC, AD, AG, DF, FG, GL, GL

i know how to use the OR but i was wondering is there is a more simple solution like to declare an array with these values an check it against the cell value in the loop.

any ideas?

 

[Jon von der Heyden]

I'd go with OR

=OR(A1={"AB";"AC";"AD";"AG";"DF";"FG";"GL"})

 

[Kaps_mr2]

Look at the INSTR function. Think that is what you need.

Regards

Kaps

 

[Jon von der Heyden]

If I were doing this in VBA then I probably would go along the lines of your original thinking:

Sub test()
    Dim varArry As Variant: varArry = VBA.Array("AB", "AC", "AD", "AG", "DF", "FG", "GL")
    
    If IsNumeric(Application.Match([a1], varArry, 0)) Then
        'do something
    End If
End Sub

 

[neveu]

Jon,
your solution worked great.
thank you