Search for value then paste text into a cell

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,910
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

kennypete

Board Regular
Joined
Apr 19, 2008
Messages
247
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
This will do that:
VBA Code:
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:
1124358.gif
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,910
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Why do i get variable not defined ?
When i debug this line is in yellow

For Each Cell In r
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,910
Office Version
  1. 2007
Platform
  1. Windows
Thanks.
I did look right at the top of the page but explicit wasnt there.
Doing what you advised worked anyway.
 

juddaaaa

Board Regular
Joined
Jan 4, 2020
Messages
208
Office Version
  1. 365
Platform
  1. Windows
Here's another option that could be reused with different ranges, output column etc.
VBA Code:
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
VBA Code:
Sub RunIt()
  MarkSVDPaid _
    Rng:=Sheets("Sheet1").Range("R1"), _
    StopAtRow:=352, _
    OutputColumn:="Z", _
    SearchText:="SVD", _
    OutputText:="PAID"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top