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

[skaisdead22]

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

 

[MrTomasz]

Based on your description, I assume there won't be cases where apple is in both columns in one row.
Try this:

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]

Possibly (non-macro):

Book3
	A	B	C
1	FIND:	jam
2	apple, banana, berry	beans, carrots	Fruit
3	jam	carrots
4	bananas, raspberries	String beans, corn, other
5	strawberry, raspberry, jam
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IF(COUNTIF(A2:A100,"*"&B1&"*")>0,"Fruit",IF(COUNTIF(B2:B100,"*"&B1&"*")>0,"Vegetable",""))

 

[skaisdead22]

CBased on your description, I assume there won't be cases where apple is in both columns in one row.
Try this:

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]

try this:

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

 

[skaisdead22]

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]

Change ActiveSheet to Sheets("YourTabName")

 

[skaisdead22]

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?

 

[skaisdead22]

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]

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.