0

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
Alex P
  • 12,249
  • 5
  • 51
  • 70
jhew123
  • 77
  • 7
  • I think you need to put `for each cell in rng` after `Set rng` line, change the `Set cl and if not` line to `if cell.value=sFind then cell.offset(0,1).value="1" ` then put `Next` before End Sub – karma Apr 29 '20 at 10:39

1 Answers1

1

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.

Ralf S
  • 190
  • 1
  • 8
  • Thanks that works perfectly, one other thing - is it possible for sFind to be multiple values i.e "drek", "dorm","drab" etc? – jhew123 Apr 29 '20 at 10:51
  • Sure, you could use an array of strings for that, cf. [https://stackoverflow.com/questions/4228137/how-can-i-use-a-for-each-loop-on-an-array](How can I use a for each loop on an array). – Ralf S Apr 29 '20 at 11:00