PDA

View Full Version : Solved: Find text & Mark findings



countryfan_n
01-11-2009, 11:34 PM
Hello friends,

Please look at the data in column "J". I need your help in creating a code. The would look for "10714", every time it finds it, it marks "X", in the column "K" (nearby cell).

Thank you so much!
Nawaf

nst1107
01-11-2009, 11:44 PM
I don't see any file attached, but I'm guessing the code should look something like this:
Dim c As Range
For Each c In Sheet1.Range("J1:" & Sheet1.Columns("J").SpecialCells(xlLastCell).Address)
If c = 10714 Then c.Offset(, 1) = "X"
Next

countryfan_n
01-11-2009, 11:53 PM
Thanks a lot,
One problem the "10714" that I am looking for is not in the beginning nor is it at the end of the cell. that is why the code is still not working. sorry, the attachment is there now.

Thanks again!
Nawaf

GTO
01-12-2009, 12:43 AM
You could use InStr() to see if "10714" exists anywhere inside the string.

Sub ex()
Dim lngLastRow As Long
Dim rcell As Range

With ThisWorkbook.Worksheets("Sheet1")
lngLastRow = .Range("J" & .Rows.Count).End(xlUp).Row
End With


For Each rcell In ThisWorkbook.Worksheets("Sheet1").Range("J2:J" & lngLastRow)
If Not InStr(1, rcell.Text, "10714", vbTextCompare) = 0 Then
rcell.Offset(, 1).Value = "X"
End If
Next
End Sub

countryfan_n
01-12-2009, 01:06 AM
Thank a lot to all, that certainly made my day!

GTO
01-12-2009, 01:15 AM
Greetings,

You are most welcome of course. Once a question is Solved, please mark it Solved. You can do this by clicking the Thread Tools button that is at the top of your first post; and select the mark as solved option.

Mark