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]

enter the full code to check the error which you mentioned

the below line gets the last row in colum A.

lastRow = .Cells(.Rows.Count, "a").End(xlUp).Row

 

[skaisdead22]

enter the full code to check the error which you mentioned

the below line gets the last row in colum A.

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.

 

[MrTomasz]

change

Sheets("RawData")

to

With Sheets("RawData")

With statement (VBA)

Office VBA reference topic

docs.microsoft.com

 

[skaisdead22]

change

Sheets("RawData")

to

With Sheets("RawData")

With statement (VBA)

Office VBA reference topic

docs.microsoft.com

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]

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

 

[skaisdead22]

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

 

[MrTomasz]

try this:

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

 

[skaisdead22]

try this:

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!