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

 

[My Aswer Is This]

Try this:

Sub Find_Me()
'Modified  4/30/2021  10:36:32 PM  EDT
Application.ScreenUpdating = False
Dim SearchString As String
Dim SearchRange As Range
Dim Replace As String
SearchString = InputBox("Search for what value")
Replace = InputBox("Replace " & SearchString & "With what")

Dim lastrow As Long
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
Set SearchRange = Range("H2:H" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Offset(0, 1).Value = Replace
Application.ScreenUpdating = True
End Sub

 

[Natman111]

Sub Find_Me() 'Modified 4/30/2021 10:36:32 PM EDT Application.ScreenUpdating = False Dim SearchString As String Dim SearchRange As Range Dim Replace As String SearchString = InputBox("Search for what value") Replace = InputBox("Replace " & SearchString & "With what") Dim lastrow As Long lastrow = Cells(Rows.Count, "H").End(xlUp).Row Set SearchRange = Range("H2:H" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole) If SearchRange Is Nothing Then MsgBox SearchString & " Not Found": Exit Sub SearchRange.Offset(0, 1).Value = Replace Application.ScreenUpdating = True End Sub

Thanks so much, this works great

 

[My Aswer Is This]

Thanks so much, this works great

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[Balajibenz]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Hi @My Aswer Is This, I have similar requirement where if a cell in column F contains particular value then it respective cell in column X should be updated with a value.

If F column cell contains values such as 44 or 56 or 79 then X column cell should be updated as "Yes" else it should be "No".

Can you please help me with this.

 

[My Aswer Is This]

Hi @My Aswer Is This, I have similar requirement where if a cell in column F contains particular value then it respective cell in column X should be updated with a value.

If F column cell contains values such as 44 or 56 or 79 then X column cell should be updated as "Yes" else it should be "No".

Can you please help me with this

Since this original question was posted nearly a year ago it would be best to make a new posting with your question. And please be more specific.
Like you said:
such as 44 or 56 or 79
I could write the script using 44 56 and 79 and have you modify the script to your specific requirements.
So make a new posting and I will see it and see if I can help you.

 

[My Aswer Is This]

Well I decided to help using what you gave me. Modify to your needs:
Try this:

Sub Look_For()
'Modified 3/21/2022  11:01:05 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row

For i = 1 To Lastrow
    Select Case Cells(i, "F").Value
        Case "44", "56", "79"
            Cells(i, "X").Value = "Yes"
            
        Case Else
            Cells(i, "X").Value = "No"
        End Select
Next
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

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

 

[My Aswer Is This]

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

I see this type script and for the life of me I do not understand them. Select case seems easy for me to understand.

 

[Peter_SSs]

I see this type script and for the life of me I do not understand them. Select case seems easy for me to understand.

Each to his own I guess. Simply offering an alternative and the OP can choose the one they want.

Whilst Select Case is fine and sure, easy enough to follow, mine looks to me like an easy worksheet formula equivalent ....

=IF(ISNUMBER(FIND("|"&F1&"|","|44|56|79|")),"Yes","No")

.... and since Excel will assess the whole column at once, that seems like a good idea to me.

A further advantage of options for the OP, or other readers, is in case the actual data may be very large.
For 100,000 rows: 5 seconds for one and 0.3 seconds for the other, might be a valid reason for choosing a particular option.