Macro that will search 2 columns for a certain value and return a set value depending on which column it is located in.

skaisdead22

New Member
Joined
Dec 17, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
Hello,

Lets say I have 2 columns (A and B) containing different strings in either column.

I have a certain value (ex. "apple") that I would like to search these two columns for. The specific cells may contain additional data (ex. A1 = "apple, banana, berry). Depending on which column my value (apple) is found in, I would like the macro to return a value (ex. "fruit") if it located in column A and return (ex. "vegetable) if it is found in column B.

I would like results ("fruit" or "vegetable") to go into a new column (C) next to the 2 columns that were just searched.

Happy to provide any additional context as needed.

I appreciate all the skills and support of this community!

Thanks,
Skaisdead22
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows
Based on your description, I assume there won't be cases where apple is in both columns in one row.
Try this:

VBA Code:
Sub test()
    Dim lastRow As Long
    Dim word As String
    
    word = "apple"
    
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
        For i = 2 To lastRow
            If InStr(1, .Range("a" & i).Value, word) > 1 Then
                .Range("c" & i).Value = "fruit"
            ElseIf InStr(1, .Range("b" & i).Value, word) > 1 Then
                .Range("c" & i).Value = "vegetable"
            End If
        Next i
    End With
End Sub
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,587
Office Version
  1. 365
  2. 2010
Possibly (non-macro):

Book3
ABC
1FIND:jam
2apple, banana, berrybeans, carrotsFruit
3jamcarrots
4bananas, raspberriesString beans, corn, other
5strawberry, raspberry, jam
Sheet1
Cell Formulas
RangeFormula
C2C2=IF(COUNTIF(A2:A100,"*"&B1&"*")>0,"Fruit",IF(COUNTIF(B2:B100,"*"&B1&"*")>0,"Vegetable",""))
 

skaisdead22

New Member
Joined
Dec 17, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
CBased on your description, I assume there won't be cases where apple is in both columns in one row.
Try this:

VBA Code:
Sub test()
    Dim lastRow As Long
    Dim word As String
   
    word = "apple"
   
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
        For i = 2 To lastRow
            If InStr(1, .Range("a" & i).Value, word) > 1 Then
                .Range("c" & i).Value = "fruit"
            ElseIf InStr(1, .Range("b" & i).Value, word) > 1 Then
                .Range("c" & i).Value = "vegetable"
            End If
        Next i
    End With
End Sub

I think this is very close. Correct in your assumption "apple" will not occur in the same row in columns A and B. However, my sheet contains much more data than just columns A&B so I want to make sure this Macro only looks at 2 defined columns.

In addition, rather than defining the search string in the macro (word = "apple") could I have it reference a cell on a particular tab of my workbook. That way the user could change the value and perform the macro with various inputs?
 

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

try this:

word = Sheets("YourTabName").Range("a1").value
 

skaisdead22

New Member
Joined
Dec 17, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
try this:

word = Sheets("YourTabName").Range("a1").value
Awesome! Thank you so much already for your help.

With respect to " With ActiveSheet", I would like it to run on a specific tab (sheet) as these Macros are being linked to buttons on an instruction page rather than being ran by someone with knowledge of the code.
 

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Change ActiveSheet to Sheets("YourTabName")
 

skaisdead22

New Member
Joined
Dec 17, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
When I try to run the macro with the edits to accommodate my tab names and data locations, I immediately get the following error.

Any idea without seeing the remainder of my code why the debugger would immediately stop the macro starting on the first line?

1611092876495.png

1611092802507.png
 

skaisdead22

New Member
Joined
Dec 17, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
When I try to run the macro with the edits to accommodate my tab names and data locations, I immediately get the following error.

Any idea without seeing the remainder of my code why the debugger would immediately stop the macro starting on the first line?

View attachment 30115
View attachment 30113
Also
Change ActiveSheet to Sheets("YourTabName")
Also can you explain " lastRow = .Cells(.Rows.Count, "a").End(xlUp).Row"

I believe I need to change the "a" value to accommodate my data's location and workbook structure.
 

skaisdead22

New Member
Joined
Dec 17, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
Hello,

Lets say I have 2 columns (A and B) containing different strings in either column.

I have a certain value (ex. "apple") that I would like to search these two columns for. The specific cells may contain additional data (ex. A1 = "apple, banana, berry). Depending on which column my value (apple) is found in, I would like the macro to return a value (ex. "fruit") if it located in column A and return (ex. "vegetable) if it is found in column B.

I would like results ("fruit" or "vegetable") to go into a new column (C) next to the 2 columns that were just searched.

Happy to provide any additional context as needed.

I appreciate all the skills and support of this community!

Thanks,
Skaisdead22
Looking to bump this.

For further context, my workbook has many tabs. The tab with the target data has many other columns aside from the 2 containing the pertinent data.

I am building an instructions page to help the user navigate the macros rather than running them on the active page where the data sits.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,858
Messages
5,627,286
Members
416,236
Latest member
Lynchbox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top