VBA to search a column by defined string case and write to next cell

[bettlejus]

Hello,

I have the following table, column A contains string that repeats in some form, like front, front-l30, the word "front" is found in all, and I want a code that loops each row and it it finds the whole specified word in a case loop than it write to column B a specific value.
To make it clear, for the bellow example:

If search string="front" then write "10"
else if search string="front-l30 then write "20"
else if search string='front-r30 then write "30"

I tried to combine some code found here but without success.

Any help is appreciated.

Thank you!

Front	10
Front-L30	20
Front-R30	30

 

[Joe4]

Why do you need code instead of a simple formula, i.e.

=IF(A1="Front",10,IF(A1="Front-L30",20,IF(A1="Front-R30",30,"")))

If you really want to use VBA, I would recommend using a CASE statement.
See: MS Excel: How to use the CASE Statement (VBA)

 

[bettlejus]

there are more text strings in cells, I cannot use search, find funtions as they don’t use whole word search.

I want to search only those string inside a bigger string like “side front-l30”, it’s not only “front-30” there

 

[Joe4]

So you are saying that your data is NOT like what you posted in your original thread?
Can you post a more realistic data example?

Also, exactly how many values are there to look for?
Where is the list of values that should be returned for each one?
Is this in a table somewhere, or do you plan to code for each one explicitly in VBA?

 

[bebo021999]

Is it case:
###Front###00###?
or alway: xxxFront-#00?

 

[bettlejus]

Sorry, I now realized reading post that it's incomplete, was

xxxfrontxxx	10
yyyfront-l30yyy	20
zzzfront-r30zzz	30
aaaback-l30aaaa	40

in a hurry, let me show you exactly.

there will be a fixed and hardcoded in the case funtion mapping legend like this:

front=10
front-l30=20
front-r30=30
back-l30=40

About 10 mapping values, mapping string are fixed, like alway "front-l30" not "front l30" or "l30 front" etc.

Thank you!

 

[bebo021999]

Using UDF (user defined function)

Option Explicit
Function GetValue(ByVal colA As Range)
Dim val, str, i&, item As String
str = Array("front", "front-I30", "front-r30", "back-I30") ' add more as per actual situation
val = Array(10, 20, 30, 40) ' values (val) in same order with string (str)
    For i = 0 To UBound(str)
        If InStr(1, colA, str(i)) Then GetValue = val(i)
    Next
End Function

With A1 contains string, in B1:
=GetValue(A1)