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>

 

[hiker95]

Shawn23,

Sample raw data:

Excel 2007
	A	B	C	D
1	Company	PRODUCTS	ITEM	PRICE
2	ABC	SMALL CARS		100
3	ABC	MEDIUM CARS		200
4	ABC	LARGE CARS		300
5	ABC	CLOTHES		400
6	ABC	ELECTRONICS		500
7
Sheet1

After the macro:

Excel 2007
	A	B	C	D
1	Company	PRODUCTS	ITEM	PRICE
2	ABC	SMALL CARS	SMALL	100
3	ABC	MEDIUM CARS	MEDIUM	200
4	ABC	LARGE CARS	LARGE	300
5	ABC	CLOTHES		400
6	ABC	ELECTRONICS		500
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub FindSize()
' hiker95, 04/11/2013
' 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, s, i As Long
s = Array("small", "medium", "large")
For Each c In Range("B2", Range("B" & Rows.Count).End(xlUp))
  For i = LBound(s) To UBound(s)
    If InStr(UCase(c), UCase(s(i))) > 0 Then
      c.Offset(, 1) = UCase(s(i))
      Exit For
    End If
  Next i
Next c
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindSize macro.

 

[hiker95]

Shawn23,

I missed this part CLOTHES will return OTHER.

Be right back.

 

[hiker95]

Shawn23,

After the updated macro:

Excel 2007
	A	B	C	D
1	Company	PRODUCTS	ITEM	PRICE
2	ABC	SMALL CARS	SMALL	100
3	ABC	MEDIUM CARS	MEDIUM	200
4	ABC	LARGE CARS	LARGE	300
5	ABC	CLOTHES	OTHER	400
6	ABC	ELECTRONICS	OTHER	500
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub FindSizeV2()
' hiker95, 04/11/2013
' 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, s, i As Long
s = Array("small", "medium", "large")
For Each c In Range("B2", Range("B" & Rows.Count).End(xlUp))
  For i = LBound(s) To UBound(s)
    If InStr(UCase(c), UCase(s(i))) > 0 Then
      c.Offset(, 1) = UCase(s(i))
      Exit For
    End If
  Next i
  If c.Offset(, 1) = "" Then c.Offset(, 1) = "OTHER"
Next c
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindSizeV2 macro.

 

[hiker95]

Shawn23,

I got it now.

After the latest macro:

Excel 2007
	A	B	C	D
1	Company	PRODUCTS	ITEM	PRICE
2	ABC	SMALL CARS	SMALL	100
3	ABC	MEDIUM CARS	MEDIUM	200
4	ABC	LARGE CARS	LARGE	300
5	ABC	CLOTHES	OTHER	400
6	ABC	ELECTRONICS		500
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub FindSizeV3()
' hiker95, 04/11/2013
' 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, s, i As Long
s = Array("small", "medium", "large")
For Each c In Range("B2", Range("B" & Rows.Count).End(xlUp))
  For i = LBound(s) To UBound(s)
    If InStr(UCase(c), UCase(s(i))) > 0 Then
      c.Offset(, 1) = UCase(s(i))
      Exit For
    End If
  Next i
  If UCase(c) = "CLOTHES" Then c.Offset(, 1) = "OTHER"
Next c
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindSizeV3 macro.

 

[Shawn23]

Thank you so much!

 

[Shawn23]

Hey Hiker95,

Sorry I have one more question.

Instead of using this line of code ---> If UCase(c) = "CLOTHES" Then c.Offset(, 1) = "OTHER"

Is there a way I could return "OTHER" if "small", "medium", or "large" isn't found?

Again, Thank you.

 

[hiker95]

Shawn23,

You could use the below code from reply #4.

Option Explicit
Sub FindSizeV2()
' hiker95, 04/11/2013
' 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, s, i As Long
s = Array("small", "medium", "large")
For Each c In Range("B2", Range("B" & Rows.Count).End(xlUp))
  For i = LBound(s) To UBound(s)
    If InStr(UCase(c), UCase(s(i))) > 0 Then
      c.Offset(, 1) = UCase(s(i))
      Exit For
    End If
  Next i
  If c.Offset(, 1) = "" Then c.Offset(, 1) = "OTHER"
Next c
End Sub

 

[colaps]

Hello,

This code is awesome, can i please ask for a favor? I`m bashing my had on the wall for like 2 days and can`t figure out how to use the code but instead of declaring the search items ( "small", "medium", "large" ) i need to have them somewhere in column J let`s say and the array should get them from there instead from the code. The idea is that the end-user can add new search items just by adding the item to the column. I hope that this makes sense to you. Again thanks.

 

[hiker95]

colaps,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

We can not tell what worksheet(s), cells, rows, columns, your raw data is in.

And, we can not tell what the results should look like.

Can you post a screen shot of what your data looks like?

Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-...forum-use.html

Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets.

If the workbook contains confidential information, you could replace it with generic data.