search column for data & add name in cells next to them

[berty2000]

Hi guys

Trying to make a macro that will search a column for names and when it finds them paste the word "Task Complete" in the cells next to the names.

Any blank cells leave blank.

My thinking was add a formula in the column M4:M200 that would search for any entry in the column and if found in column N4:N200 enter the word next to it.

This is what iv'e played around with but can't get right

Sub checknames()

' Select all blank cells in range M4:M200
Range("M4:M200").SpecialCells(xlCellTypeBlanks).Select
' Enter formula in these blank cells
Selection.Formula = "=IF(ISERROR(SEARCH("",M4,1)),"","Task Complete ")"

End Sub

 

[Leith Ross]

Hello berty2000,

Can you give me an example using the cells? Which column has the names?

 

[berty2000]

names are in column M going down

 

[Leith Ross]

Hello berty2000,

I'm still having trouble understanding this. What is the search term and where is it located?

Let's say Berty is in M4. Would the macro then search N4:N200 for this name?

 

[berty2000]

Does this make any sense

 

[berty2000]

The names go down in column M like Peter jan sue then in column N alongside the names I want task complete to come up

 

[Leith Ross]

Hello berty2000,

It looks like you want the "Task Complete" to appear in column "N" whenever a the adjacent cell in column "M" is not empty. Add this formula to cell N4 then drag it down to N200.

=IF(M4="","","Task Complete")​

 

[berty2000]

Thats correct but I need to be able to run it all from a macro so i can press a button and it will do it automatically when I need it

 

[Leith Ross]

Hello berty2000,

Sorry, you did you wanted a macro.

Sub MarkCompleted()

    Dim Cell As Range
    
        For Each Cell In Range("M4:M200")
            If Cell <> "" Then Cell.Offset(0, 1) = "Task Complete"
        Next Cell

End Sub

 

[berty2000]

Wow that is exactly what I was after and you have make it look well easy

Thank you very much