Find a text within a cell and return a text in the adjacent column using VBA

[Shawn23]

Hi,

How do I search column B (Products), for a certain word and return it to column C (Item) using VBA?
In VBA, I would like it to search for certain words such as "Small", "Medium", and "Large". If none of these words are found, I would like it to return "Other".

So if the first product is SMALL CARS, it will return SMALL under ITEM. CLOTHES will return OTHER.

I know I could just use the LOOKUP function, but I would like to build this under VBA.

Thank you!

Company	PRODUCTS	ITEM	PRICE
ABC	SMALL CARS		100
ABC	MEDIUM CARS		200
ABC	LARGE CARS		300
ABC	CLOTHES		400
ABC	ELECTRONICS		500

<tbody>
</tbody>

 

[colaps]

Hello,

Excell 2016, Windows 10, PC

Link provided by you didn`t work sadly but here is the link to my file:

https://www.dropbox.com/s/xypjwola7dgowoi/Book1.xlsx?dl=0


Column A my raw data
Column E search criteria
Column B the result that i`m looking for.

 

[colaps]

Hello,

Excell 2016, Windows 10, PC

Link provided by you didn`t work sadly but here is the link to my file:

https://www.dropbox.com/s/xypjwola7dgowoi/Book1.xlsx?dl=0


Column A my raw data
Column E search criteria
Column B the result that i`m looking for.

is the information complete ? is it understandable what i need now ?

Thanks in advance.

 

[hiker95]

colaps,

Here is a macro solution for you to consider, that will run in worksheet S1.

Please try the following macro on a copy of your raw data workbook.

Sub FindFamily()
'hiker95 , 6/20/2019
'http://www.mrexcel.com/forum/excel-questions/696585-find-text-within-cell-return-text-adjacent-column-using-visual-basic-applications.html
Dim c As Range, f, i As Long
f = Array("flower", "car", "tree")
With Sheets("S1")
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    For i = LBound(f) To UBound(f)
      If InStr(c, f(i)) > 0 Then c.Offset(, 1) = f(i)
      If InStr(UCase(c), UCase(f(i))) > 0 Then c.Offset(, 1) = f(i)
    Next i
  Next c
End With
End Sub

 

[colaps]

Hello,

This works but my request was to not use f = Array("flower", "car", "tree") inside the code ( i will need to modify this when new families will be introduced and ofc not everyone will be able to read the code and made the adjustments as needed. From here the idea to read a desired column that contains the arrays, a range.

i need to read "flower","car","tree" from Column E. I tried to modify the code to read the arrays from a range ( E2:E10 ) but without luck.

Thanks for your effort.

 

[hiker95]

colaps,

Here is a new macro solution for you to consider, that will run in worksheet S1.

Please try the following macro on a copy of your raw data workbook.

Sub FindFamily_V2()
'hiker95 , 6/21/2019
'http://www.mrexcel.com/forum/excel-questions/696585-find-text-within-cell-return-text-adjacent-column-using-visual-basic-applications.html
Dim c As Range, f, i As Long, lr As Long
With Sheets("S1")
  lr = .Cells(Rows.Count, 5).End(xlUp).Row
  f = .Range("E2:E" & lr)
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    For i = LBound(f) To UBound(f)
      If InStr(c, f(i, 1)) > 0 Then c.Offset(, 1) = f(i, 1)
      If InStr(UCase(c), UCase(f(i, 1))) > 0 Then c.Offset(, 1) = f(i, 1)
    Next i
  Next c
End With
End Sub

 

[hiker95]

colaps,

The latest macro will adjust to the number of entries in worksheet S1, in column E, E2 to the last used row in column E.

 

[colaps]

This works perfect ! Thanks big time.

Regards.

 

[hiker95]

This works perfect ! Thanks big time.

Regards.

colaps,

Thanks for the feedback.

You are very welcome.

And, come back to MrExcel anytime.