Find values that not start with specific letters I need find values that not sta

Question

Find values that not start with specific letters

I need find values that not start with letter A,B,C,F,O in column J and replace the name to cell in column D make a reference from cell of the column J.

I find thi code but change the name in the same column I need change the cell from the column D.

For Each cl In Range("J2", Range("J2").End(xlDown))
    If UCase(Left(cl.Value, 1)) <> "A" And UCase(Left(cl.Value, 1)) <> "B" And UCase(Left(cl.Value, 1)) <> "F" And UCase(Left(cl.Value, 1)) <> "O" Then
        cl.Value = "SF"
'Range("D" & r).Value = "SF" '<=== I add this line but not work

    End If
Next cl

Thanks !!

Explanation / Answer

Hi..

Hi,

Try it this way.

NOTE This will change Col J and Col D to SF

Sub somesub()
Dim cl As Range, LastRow As Long
LastRow = Cells(Cells.Rows.Count, "J").End(xlUp).Row,
For Each cl In Range("J2:J" & LastRow)
     If UCase(Left(cl.Value, 1)) <> "A" And UCase(Left(cl.Value, 1)) <> "B" And _
     UCase(Left(cl.Value, 1)) <> "C" And UCase(Left(cl.Value, 1)) <> "F" _
     And UCase(Left(cl.Value,, 1)) <> "O" Then
         cl.Value = "SF"
        Range("D" & cl.Row).Value = "SF" '<=== I add this line but not work
    End If
Next cl
End Sub

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.