String comparison w/ mulitple values 1

[ceej]

In Excel I need to test whether cell A1 contains either "AA" or "BB" or "CA" or "AT".

If it does than cell B1 will contain a value based on what was the matching string in A1.

Can Excel do this out of the box? The Search function doesn't allow multiple values to match.

How would this be done in VB?

Thanks!

 

[SkipVought]

Hi,

This is really a spreadsheet functionality question Forum68, rather than a VBA code question.

Set up a table with lookup values and return values. Lookup the value in the table and return the corresponding value. Check out the VLOOKUP function.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[mintjulep]

=IF(OR(A1="AA",A1= "BB", A1="CA", A1="AT"),"Whatever", "Not")

 

[ceej]

I could probably do either but I will have to use several columns since I have to test for multiple values:

1. Sometimes I have to check the first 2 digits
2. Other times I have to check the first 3 digits

Cell A1 may be "AA-12345" or "A-12345" or "L12098765"

If A1 = "AA" than value = "Red"
If A1 = "A-" than value = "Blue"
If A1 = "L12" than value = "Green"

Is searching for text in VBA hard?

 

[PHV]

Is searching for text in VBA hard?
No.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

If [A1] Like "AA*" Then
    value = "Red"
ElseIf [A1] Like "A-*" Then
    value = "Blue"
ElseIf [A1] Like "L12*" Then
    value = "Green"
Else
    value = "none"
End If

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ceej]

Aaah! Now I understand.

I had looked at Choose and Switch but they were too exact.

The "Like" code makes sense.

Thanks.