If statements in VBA to look at multiple criteria without a nested formula

Hi,

I would like to write in VBA a list to advise IF contains this then return this for a number of outcomes. I have seen in the past this used to look for postcodes and then using the formula bar you can reference the code to check a cell rather than nesting IF statements.

Basically I want to check if cell contains APP then return APP, Else IF cell contains CARD then return CARD etc etc.

In excel I'd then just input

=(subname(CELL))

Thanks

Ok here's a sample,

Thanks

Hi !

You can place your arguments list in a cells range and combine IFERROR and MATCH function for example …

Originally Posted by Keebellah

If the text yiu want is always in the middel I would do it like this:

Public Function getTag(myString As String) As String
Dim fstring As Variant
fstring = Split(myString, " ")
getTag = fstring(LBound(fstring) + 1)
End Function

Copy the function into a vba module

then in cell

D2=getTag(B2)

and you get Dog

There is no test to check B2 is empty or not, just quick-and-dirty, split the text and take the text in the center.
If the text is always the second word, it will work with longer texts too

If not then you will always be stuck with one or more if then else
Give it a try and let me know

Keebellah, I'm sorry but the text could be anywhere and the LEN functions advises the text could be anywhere in up to 1007 characters.They are not also split by spaces and the text may be within other characters. I also need the return value to be specifically written as while it will start with IF dog then DOG, we created incidents for these and DOG would then refer to incident xxx so I need to be able to configure that as I go.

This function then would be housed in numerous documents but I will update the VBA code only once to update.

Thanks Keebellah.

Marc L, I'm aware there are a few ways to do this using existing formulas however due to the expanding and complex nature of my source data I need to house it in VBA