How can Excel rewrite a copied cell value based on my instructions?

[xThomi]

Hi

I am currently copying information from a different source into Excel. The source gives me values which i want to rewrite. An example would be that the source gives me a value in column M that is "1". But i need to rewrite that information into "Completed". This is now done manually but i was hoping there could be some kind of macros that could help me automate this.

 

[Fluff]

You can record a macro for that.
Turn macro recorder on, then Ctrl H put 1 in the Find What box & put Completed in the Replace with box, click Replace All & finally stop the recorder.

 

[xThomi]

Hi FLuff

Thnx!


I tried this now but when i run the macro (i've set it to run on command Ctrl+p) then i get a Run-time error '91' Object variable or With block variable not set

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+P
'
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Replace What:="1", Replacement:="Complete", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.FindNext(After:=ActiveCell).Activate
End Sub

 

[Fluff]

Looks like you clicked Find & then replace, rather than replace all.
Try

Sub Macro1()
    Range("M:M").Replace What:="1", Replacement:="Complete", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[xThomi]

It works now, thank you!

A follow up question: Is it possible to run the macro automatically everytime a line is copied in?

 

[Fluff]

You could use this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
   Range("M:M").Replace What:="1", Replacement:="Complete", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
Application.EnableEvents = True
End Sub

It needs to go in the sheet module rather than a standard module.
Right click the tab you want it to work on, select view code & paste the code into the window that opens up.
BUT be aware that this will run whenever you make any changes to that sheet.