PDA

View Full Version : Search column for text match, if found add text into row



markpem
12-18-2014, 04:09 AM
Hello

Would anyone be able to help me with some code:


I have text in Column I and if it matches GREEN it needs to paste the word TRUE in column F but on the same row? (It would need to search until the last entry in column I)


Please see screenshot, Thankyou in advance

12627

JKwan
12-18-2014, 07:48 AM
formula solution, enter formula and fill down
=IF(I1="GREEN",TRUE,"")

VBA solution

Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function
Sub LookForGreen()
Dim LastRow As Long
Dim lRow As Long

LastRow = FindLastRow(ActiveSheet, "I")
For lRow = 1 To LastRow
If UCase(Cells(lRow, "I")) = "GREEN" Then
Cells(lRow, "F") = True
End If
Next lRow
End Sub

sktneer
12-29-2014, 10:15 AM
Another approach.....


Sub MatchText()
Dim rng As Range, cell As Range
Dim lr As Long, r As Long
Dim Text As String
lr = Cells(Rows.Count, "I").End(xlUp).Row
Set rng = Range("I1:I" & lr)
Text = "GREEN"
With rng
Set cell = .Find(Text)
If Not cell Is Nothing Then
r = cell.Row
Do
cell.Offset(0, -3) = True
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And r <> cell.Row

End If
End With
End Sub

ashleyuk1984
12-29-2014, 11:16 AM
Sub FindGreen()
Dim LastRow As Integer
Dim x As Integer

LastRow = Range("I" & Rows.Count).End(xlUp).Row

For x = 1 To LastRow
If Range("I" & x).Value = "GREEN" Then Range("F" & x).Value = "TRUE"
Next x

End Sub

http://www.ultraimg.com/images/Capturec2f97.png