CODEWITHSUNDEEP
vbaexcel
user3216149
user3216149

Reputation: 21

Seemingly simple task of looping for a string in VBA

I am having some trouble getting a loop to work in Excel VBA which searches cell E in each row for a string and then puts that string in cell N of that row. Code is below:

Dim i As Long
For i = 1 To Rows.Count
Next i
If Cells(i, 5).InStr(1, ActiveSheet.Range("ActiveCell").Value, "Smith") > 0 Then
Cells(i, 14).ActiveCell = "Smith"
End If

Can anyone please give insight as to where I am going wrong?

Upvotes: 1

Views: 79

Answers (1)

Andrew
Andrew

Reputation: 2335

Try:

Dim i As Long
For i = 1 To Rows.Count
    If Cells(i, 5).InStr(1, ActiveSheet.Range("ActiveCell").Value, "Smith") > 0 Then
        Cells(i, 14).ActiveCell = "Smith"
    End If
Next i

You were ending your loop before it even did anything.

I think you also need to replace 

Cells(i, 14).ActiveCell = "Smith"

With

Cells(i, 14).Value = "Smith"

Upvotes: 3

Related Questions