CODEWITHSUNDEEP
vbaexcel
David
David

Reputation: 5

vba how to search for a word and then add a column with a specific term?

I'm currently trying to find a way to search for a phrase "term" in column "B" and then to add some text "new text" in a new column "I". I'm new to VBA and the code I have currently doesn't add any text:

    Sub addclm()

Dim row As Long
Dim column As Long
Dim strsearch As String
Dim rfind As Range
Dim sfirstaddress As String

strsearch = "term"
row = Sheet4.Range("I4").row
column = Sheet4.Range("I4").column


With Sheet4.Columns("B:B")
    Set rfind = .Find(strsearch, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)

If Not rfind Is Nothing Then
sfirstaddress = rfind.Address
Sheet1.Cells(row, column) = "new text"
row = row + 1


End If
End With
End Sub

Thanks in advance for the help!

Upvotes: 0

Views: 1415

Answers (1)

urdearboy
urdearboy

Reputation: 14580

It's a little unclear what you want to do exactly, but this is my best guess at what you are wanting given the info you have provided. Paste this in a module, not a worksheet.

  1. Determine what range to scan (Sheet4, ColB, Last Used Cell up to B2)
  2. Loop through each Cell looking for "term"
  3. If found, go to sheet1, paste value "new text" on corresponding ROW
  4. If not found, do nothing

  5. Loop 2-4 until all range is covered 

    Option Explicit
Sub addclm()

Dim WB As Workbook
Set WB = ThisWorkbook

Dim strsearch As String
strsearch = "term" 'what to search for in each cell in B

Dim LRow As Long 'Determine the last row in B to limit loop
LRow = WB.Sheets("Sheet4").Range("B" & WB.Sheets("Sheet4").Rows.Count).End(xlUp).row

Dim MyCell As Range
Dim MyRange As Range
Set MyRange = WB.Sheets("Sheet4").Range("B2:B" & LRow) 'Where to loop (From bottom last row to B2)

For Each MyCell In MyRange
    If InStr(MyCell.Text, strsearch) > 0 Then
        WB.Sheets("Sheet1").Range("I" & MyCell.row) = "new text"
    End If
Next MyCell    
End Sub

Upvotes: 1

Related Questions