VBA add text to cell based on value of another cell

[khanh_n]

Hi all,
I'm a complete beginner with VBA. Hoping for your help.

I found the below VBA, which I tested and works fine.

Sub X()
Dim lngRow As Long
Dim BotRow As Long

Cells(Rows.Count, "K").Select
Selection.End(xlUp).Select
BotRow = Selection.Row
For lngRow = 1 To BotRow
If UCase(Cells(lngRow, "K")) = "YES" Then
Cells(lngRow, "F") = Cells(lngRow, "F") & "************"
End If
Next
End Sub
After modifying to below, it no longer works.

Sub AddText()

Dim lngRow As Long
Dim BotRow As Long

Cells(Rows.Count, "N").Select
Selection.End(xlUp).Select
BotRow = Selection.Row
For lngRow = 1 To BotRow
If UCase(Cells(lngRow, "N")) = "remove" Then
Cells(lngRow, "H") = Cells(lngRow, "H") & "-Removed"
End If
Next
End Sub

Basically I want to add '-Removed' to existing cell in column H on the same row if the word 'remove' is found in cell in column N.

Any help would be much appreciated. Thanks.

 

[RCBricker]

only issue I see between the two is that you are looking at the UCASE of a cell but looking for lower case word. UCASE changes the value in the cell to all upper case. So it would never match to "remove"

try:

Sub AddText()

 Dim lngRow As Long
 Dim BotRow As Long

 Cells(Rows.Count, "N").Select
 Selection.End(xlUp).Select
 BotRow = Selection.Row
 For lngRow = 1 To BotRow
 If UCase(Cells(lngRow, "N")) = "REMOVE" Then
 Cells(lngRow, "H") = Cells(lngRow, "H") & "-Removed"
 End If
 Next
 End Sub

 

[khanh_n]

Hi all, all good I've figured it out, UCase is Uppercase. Cheers.

 

[khanh_n]

Thanks RCBricker

 

[khanh_n]

Hi RCBricker, I tested it after changing it to LCase and it works if the cell only contains "Remove". But I realize "Remove" is not always at the beginning of the cell, also there's sometimes other text in the cell as well, so it doesn't work properly.
I tried using wildcard "*removed*", doesn't seem to work. Any suggestions please?

 

[RCBricker]

try this

Sub AddText()

 Dim lngRow As Long
 Dim BotRow As Long

 Cells(Rows.Count, "N").Select
 Selection.End(xlUp).Select
 BotRow = Selection.Row
 For lngRow = 1 To BotRow
 If InStr(1, Cells(lngRow, "N").Value, "Remove") > 0 Then
 Cells(lngRow, "H") = Cells(lngRow, "H") & "-Removed"
 End If
 Next
 End Sub

 

[khanh_n]

It works, thanks heaps for that RCBricker

 

[RCBricker]

Glad to help