Using a String Value as a Condition to Alter the Value of Another Cell

[Hfnieddu]

Hello All, I've spent my entire morning trying to find a way to do the following (sorry about the terrible syntax here, I'm new at this)

Sub()
For all cells in a column
If the cell contains the string "hello world" Then
Move one cell to the right and enter the letter "n"
End If
Next Cell
End Sub()

Here is the hammered script I'm trying to use. I've never done anything like this before. Feel free to send me in another direction:


Sub assetType()
For Each cell In Range("B2:B40") 'the range I'm going for is basically all of column B
If InStr(1, cell, "hello world", 1) Then
ActiveCell.Offset(0, 1).Select
ActiveCell = "n"
ActiveCell.Offset(1, -1).Select
End If
Next cell
End Sub

 

[arguros]

Sub test()
Dim rng As Range
For Each rng In Range("B1:B10")
If InStr(1, rng.Value, "hello world", 1) Then
rng.Cells(1, 2) = "n"
End If
Next
End Sub

 

[iyyi]

Try

Sub assetType()
For Each Cell In Range("B2:B40") 'the range I'm going for is basically all of column B
    If InStr(1, Cell, "hello world", 1) Then
        Cell.Offset(0, 1) = Cell.Value
        Cell.Value = "n"
    End If
Next Cell
End Sub

 

[circledchicken]

Hi and welcome,

Another approach you might want to try if you were looking at a much larger range of cells than your example is something like this:

Sub assetType()

Dim iLoop       As Long
Dim i           As Long
Dim rNa         As Range

iLoop = WorksheetFunction.CountIf(Columns("B"), "*hello world*") ' count the instances of 'hello world'
Set rNa = Range("B1") ' set the cell to start the search after
For i = 1 To iLoop ' loop through each cell in column B that contains 'hello world'
    Set rNa = Columns("B").Find(What:="hello world", After:=rNa, _
                                LookIn:=xlValues, LookAt:=xlPart, _
                                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    rNa.Offset(0, 1) = "n" ' put an n in the adjacent cell
Next i

End Sub

This is based on a discussion here about efficient use of loops, that you may find helpful:
Excel VBA Loops: Correct/Efficient Uses of Excel Loops. Do, For Each and While Loops

 

[Hfnieddu]

Thanks all, a bunch of good suggestions I'm going to try this morning. I also brought in a partner/programmer from work who is whipping up a solution. I will try to share what ultimately works here.