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
 

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows
enter the full code to check the error which you mentioned

the below line gets the last row in colum A.
VBA Code:
lastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

skaisdead22

New Member
Joined
Dec 17, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
enter the full code to check the error which you mentioned

the below line gets the last row in colum A.
VBA Code:
lastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
Sub TestFruitVeg()
Dim lastRow As Long
Dim word As String

word = Sheets("Instructions").Range("c4").Value

Sheets ("RawData")
lastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
For i = 2 To lastRow
If InStr(1, .Range("j" & i).Value, word) > 1 Then
.Range("l" & i).Value = "fruit"
ElseIf InStr(1, .Range("k" & i).Value, word) > 1 Then
.Range("l" & i).Value = "vegetable"
End If
Next i
End With
End Sub


This is the full code I have presently.

I changed the "word" section to "c4" to match the location of my search value. In addition, I changed the Range "j" "k" and "l" to match the 2 pertinent columns in my example (columns j & k) and the output column where I want the default values to go (column l).

This could be complete incorrect. But even unaltered the code kept providing an error with the top sub line highlighted.
 

skaisdead22

New Member
Joined
Dec 17, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
change
VBA Code:
Sheets("RawData")
to
VBA Code:
With Sheets("RawData")

1611173192886.png

I made the change as directed. This is the new error I am receiving. Do I need to define the specific cell with my search string as a variable and then reference that as my word?
 

MrTomasz

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

ADVERTISEMENT

check whether your tab has the same name, maybe there is a typo
 

skaisdead22

New Member
Joined
Dec 17, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
That was my thought as well. While I am no longer getting an error message the script does not input any values in column L of the "RawData" tab
 
Last edited:

MrTomasz

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

VBA Code:
Sub TestFruitVeg()
    Dim lastRow As Long
    Dim word As String
    
    word = Trim(LCase(Sheets("Instructions").Range("c4").Value))
    
    With Sheets("RawData")
        lastRow = .Cells(.Rows.Count, "j").End(xlUp).Row
        For i = 2 To lastRow
            If InStr(1, LCase(.Range("j" & i).Value), word) > 0 Then
                .Range("l" & i).Value = "fruit"
            ElseIf InStr(1, LCase(.Range("k" & i).Value), word) > 0 Then
                .Range("l" & i).Value = "vegetable"
            End If
        Next i
    End With
End Sub
 
Solution

skaisdead22

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

VBA Code:
Sub TestFruitVeg()
    Dim lastRow As Long
    Dim word As String
   
    word = Trim(LCase(Sheets("Instructions").Range("c4").Value))
   
    With Sheets("RawData")
        lastRow = .Cells(.Rows.Count, "j").End(xlUp).Row
        For i = 2 To lastRow
            If InStr(1, LCase(.Range("j" & i).Value), word) > 0 Then
                .Range("l" & i).Value = "fruit"
            ElseIf InStr(1, LCase(.Range("k" & i).Value), word) > 0 Then
                .Range("l" & i).Value = "vegetable"
            End If
        Next i
    End With
End Sub
I believe it is finally working!

I really appreciate your time and knowledge!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,956
Messages
5,621,816
Members
415,857
Latest member
braunReivn

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