vba to search for a value in one column, if found enter specific text in the cell to the right.

[Natman111]

Hi

I have been trying to find vba code to help me look for a specific text in a column (being "H") ie looking for the text "Dog" and then once found, in the next column to the right add the text "Cat"

Column "H" might have 50 different aminals and Column "I" is blank

Help please

 

[Balajibenz]

Another option is to do the whole columns at once:

Sub CheckValues()
  With Range("F1", Range("F" & Rows.Count).End(xlUp))
    Intersect(.EntireRow, Columns("X")).Value = Evaluate("if(isnumber(find(""|""&" & .Address & "&""|"",""|44|56|79|"")),""Yes"",""No"")")
  End With
End Sub

Thank you for the response @Peter_SSs . it is only working if the cell only has 44 or 56 or 79, what i am looking for is if the string in the particular cell contains 44 in it it should be Yes else No.

 

[Peter_SSs]

what i am looking for is if the string in the particular cell contains 44 ...

Not sure if this might need some more adjustment for your Excel version but give it a try.
Post back with details if the results are incorrect.

Sub CheckValues_v2()
    Application.ScreenUpdating = False
    With Range("X1:X" & Range("F" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(COUNT(FIND({44,56,79},F1)),""Yes"",""No"")"
    .Value = .Value
  End With
  Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Not sure if you tried my script or not but now it seems like you're looking for a string that has "44"
Like if the cell had "I my age is 44 today"
So, since Peter is helping you, I will move on and find someone else to help. I'm sure Peter will has an answer for you. Take care

 

[Balajibenz]

Not sure if you tried my script or not but now it seems like you're looking for a string that has "44"
Like if the cell had "I my age is 44 today"
So, since Peter is helping you, I will move on and find someone else to help. I'm sure Peter will has an answer for you. Take care

Thank you for the response ?

 

[Peter_SSs]

Thank you for the response ?

Any test of the modified code?

 

[Balajibenz]

Any test of the modified code?

below is the code that worked for me peter.
'''
For Each myCell In myRange


If myCell Like "*44*" Or myCell Like "*56*" Or _
myCell Like "*79*" Then


myCell.Offset(0, 18).Value = "Yes"

Else
myCell.Offset(0, 18).Value = "No"

End If

Next myCell
'''

 

[Peter_SSs]

Glad you have a resolution.

There was no mention originally of myRange
If you are interested you could process the whole range at once rather than cell-by-cell like this

With Intersect(myRange.EntireRow, Columns("X"))
  .Formula = "=IF(COUNT(FIND({44,56,79}," & myRange.Cells(1).Address(0, 0) & ")),""Yes"",""No"")"
  .Value = .Value
End With

.. or it may need to be like this for your version, I can't remember the exact details of Excel 2013.

With Intersect(myRange.EntireRow, Columns("X"))
  .Cells(1).FormulaArray = "=IF(COUNT(FIND({44,56,79}," & myRange.Cells(1).Address(0, 0) & ")),""Yes"",""No"")"
  .FillDown
  .Value = .Value
End With