Search for value then paste text into a cell

[ipbr21054]

Hi,
I need to search a column for a value and if found enter some text in cell on same row.

So when written correctly it would be of the following process.

Search range R1:R352 for the value SVD and for each cell that has a match then enter the text PAID in its corresponding row in column Z

The value will always start SVD but may have other characters after.

If cell R2 has SVD then in cell Z2 enter PAID
This should continue all the way to R352
Thanks.

 

[kennypete]

This will do that:

Sub MarkSVDpaid()
    Dim s As String
    Dim r As Range
    Dim l As Long
    Set r = Range("R1:R352")
    For Each cell In r
        If Left(cell.Value, 3) = "SVD" Then
            l = Split(cell.Address, "$")(2)
            Cells(l, 26) = "PAID"
        End If
    Next
End Sub

In action, stepping it:

 

[ipbr21054]

Hi,
Why do i get variable not defined ?
When i debug this line is in yellow

For Each Cell In r

 

[kennypete]

Hi,
Why do i get variable not defined ?
When i debug this line is in yellow

For Each Cell In r

If you have Option Explicit it will. Add Dim cell as Range and you should be right.

 

[ipbr21054]

Thanks.
I did look right at the top of the page but explicit wasnt there.
Doing what you advised worked anyway.

 

[juddaaaa]

Here's another option that could be reused with different ranges, output column etc.

Sub MarkSVDPaid(Rng As Range, StopAtRow As Long, OutputColumn As String, SearchText As String, OutputText As String)
  If Rng.Row > StopAtRow Then Exit Sub
  If Left(Rng, Len(SearchText)) = SearchText Then Rng.Worksheet.Cells(Rng.Row, OutputColumn) = OutputText
  MarkSVDPaid _
    Rng:=Rng.Offset(1), _
    StopAtRow:=StopAtRow, _
    OutputColumn:=OutputColumn, _
    SearchText:=SearchText, _
    OutputText:=OutputText
End Sub

Then start it like this

Sub RunIt()
  MarkSVDPaid _
    Rng:=Sheets("Sheet1").Range("R1"), _
    StopAtRow:=352, _
    OutputColumn:="Z", _
    SearchText:="SVD", _
    OutputText:="PAID"
End Sub