VBA: If cell A contains text enter value in cell B HELP!

[nerdygirl1225]

I am new to VBA and trying to figure out the following VBA code

I have a worksheet and I want to search column J for specific words such as Apple, if Apple is found I want the word Red to populate in the corresponding cell in column K. I want the VBA to do this for multiple items in column J like this:

 

[Z51]

I assume you have a table somewhere that has fruits in one column and their associated color in another column? Otherwise how would Excel know what color a banana is? If so, you can use simple VLOOKUP formulas to pull the colors. You can use VBA, too, but it seems like overkill here.

 

[nerdygirl1225]

I assume you have a table somewhere that has fruits in one column and their associated color in another column? Otherwise how would Excel know what color a banana is? If so, you can use simple VLOOKUP formulas to pull the colors. You can use VBA, too, but it seems like overkill here.

No this is just an example. My sheets are actually patient data so I can't post an exact copy. I want to be able to tell the macro what words to search for and what word to enter into the K column if found. There is not an list of these items.

 

[Z51]

I'd recommend creating a lookup table, but if that's not feasible, and your list isn't too long, a Select Case loop through the cells might suffice. If you have a long list or thousands of lookups, using arrays would be quicker.

Sub nerdygirl()
Dim ce As Range, lastrow As Long
lastrow = Range("J" & Rows.Count).End(xlUp).Row

For Each ce In Range("J2:J" & lastrow)
    Select Case ce.Value
        Case Is = "apple"
            ce.Offset(, 1).Value = "red"
        Case Is = "banana"
            ce.Offset(, 1).Value = "yellow"
        Case Is = "cherry"
            ce.Offset(, 1).Value = "red"
        Case Is = "orange"
            ce.Offset(, 1).Value = "orange"
        Case Else
            ce.Offset(, 1).Value = "unidentified"
    End Select
Next ce
End Sub

 

[nerdygirl1225]

I am new to VBA and trying to figure out the following VBA code

I have a worksheet and I want to search column J for specific words such as Apple, if Apple is found I want the word Red to populate in the corresponding cell in column K. I want the VBA to do this for multiple items in column J like this:
View attachment 70586

I've come up with this however I now need to know how to repeat it multiple times for multiple words...

Dim SrchRng As Range, cel As Range

Set SrchRng = Range("J:J")

For Each cel In SrchRng
If InStr(1, cel.Value, "Apple") > 0 Then
cel.Offset(0, 1).Value = "Red"
End If
Next cel
End Sub

 

[nerdygirl1225]

I'd recommend creating a lookup table, but if that's not feasible, and your list isn't too long, a Select Case loop through the cells might suffice. If you have a long list or thousands of lookups, using arrays would be quicker.

Sub nerdygirl()
Dim ce As Range, lastrow As Long
lastrow = Range("J" & Rows.Count).End(xlUp).Row

For Each ce In Range("J2:J" & lastrow)
    Select Case ce.Value
        Case Is = "apple"
            ce.Offset(, 1).Value = "red"
        Case Is = "banana"
            ce.Offset(, 1).Value = "yellow"
        Case Is = "cherry"
            ce.Offset(, 1).Value = "red"
        Case Is = "orange"
            ce.Offset(, 1).Value = "orange"
        Case Else
            ce.Offset(, 1).Value = "unidentified"
    End Select
Next ce
End Sub

Thank you! This is perfect!