Find all values in range and set another cell.value in the next cell

Question

Hi I'm working on a Macro to find the word Flex in a range of strings, but I need to set another value creating a column next to it. But I can't figure out the way to iterate through all the cells up to the lastrow and for each value found set another one in the next cell and column.

Sub Method2()
Dim rng1 As Range
Dim strSearch As String
strSearch = "Flex"
Set rng1 = Range("A:A").Find(strSearch, , xlValues, xlPart)
If Not rng1 Is Nothing Then
    MsgBox "Find has matched " & strSearch & vbNewLine & "corresponding cell is " & rng1.Offset(0, 1)

Else
    MsgBox strSearch & " not found"
End If

End Sub

SJR SJR · Accepted Answer · 2018-09-03T20:14:07

If you mean that "Flex" can be found multiple times and you want a message box to appear each time then try this. If you want to change the value in the next cell then just change the line inside the Do loop.

This uses FindNext and records the address of the first find and keeps looping until we get back there, which means it's time to stop.

Sub Method2()

Dim rng1 As Range
Dim strSearch As String, s As String

strSearch = "Flex"
Set rng1 = Range("A:A").Find(strSearch, , xlValues, xlPart)

If Not rng1 Is Nothing Then
    s = rng1.Address
    Do
        rng1.Offset(0, 1).value=1            
        MsgBox "Find has matched " & strSearch & vbNewLine & "corresponding cell is " & rng1.Offset(0, 1)
        Set rng1 = Range("A:A").FindNext(rng1)
    Loop Until rng1.Address = s
Else
    MsgBox strSearch & " not found"
End If

End Sub

Find all values in range and set another cell.value in the next cell

1 Answers