CODEWITHSUNDEEP
excelvbaloopsoffset
jhew123
jhew123

Reputation: 77

Find all values with in a range and then offset

I am trying to write vba code which looks for the term "drek" in column BI. If the term is present in the cell it will then offset (0,1) the value "1".

I have used the code below, however it will only find and offset the first "drek" and then stop. I need it so it will do this with every "drek" it finds.

How might I do this? 

Sub find_drek()
    Dim rng As Range
    Dim cl As Range
    Dim sFind As String

    sFind = "drek"
    Set rng = Range("BI2", Range("BI65536").End(xlUp))
    Set cl = rng.find(sFind, LookIn:=xlValues)
    If Not cl Is Nothing Then cl.Offset(0, 1).Value = "1"
End Sub

Upvotes: 0

Views: 331

Answers (1)

Ralf S
Ralf S

Reputation: 190

You can try this:

Dim rng As Range
Dim sFind As String

sFind = "drek"

For Each rng In Range("BI2", Range("BI65536").End(xlUp))
    If Not rng.Find(sFind, LookIn:=xlValues) Is Nothing Then
        rng.Offset(0, 1).Value = "1"
    End If
Next rng

Thus, you don't need another range object.

Upvotes: 1

Related Questions