Code doesn't work anymore

[govi]

First of all a happy new year to you all.


I have a macro which always worked perfectly. Now part of it doesn't doe it's job properly anymore:

[pre]

Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 10 Then Exit Sub
If Target.Text = "FINISHED" Then
Range(Cells(Target.Row, 4), Cells(Target.Row, 16)).Copy Sheets("archief").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Range(Cells(Target.Row, 3), Cells(Target.Row, 16)).ClearContents
End If

Sheets("archief").UsedRange.Replace "FINISHED", Date

End Sub[/pre]

This part doesn't work:

Sheets("archief").UsedRange.Replace "FINISHED", Date

The word FINISHED isn't replaced by the date anymore.


Can anyone help?


Thanks, govi

 

[Hui]

Govi


This line

Sheets("archief").UsedRange.Replace "FINISHED", Date

works for me, in Excel 2010 ?


Have you stepped through the whole subroutine with F8 and manually checked it as it goes?

 

[vasim]

Sheets("archief").UsedRange.Replace what:="Finished", replacement:="Date", lookat:=xlPart, MatchCase:=False


Replace ur part with this one...

Hope it works

 

[govi]

Hi Indian, your code doesn't work either

Hi Hui, it is code I got from you about a year ago on this forum. And it worked fine until a few week ago.

I can't step to the subroutine using F8 because I need to put put FINISHED in a cell first

I just have removed the code, then put FINISHED in the desired cell, put in the code again and pressed F8...it still doesn't step through the subroutine


btw I still use Excel 2003, which has never been a problem.....

Maybe I could try to reinstall Excel?

 

[NARAYANK991]

Hi ,


Can you try by replacing the Worksheet_Change procedure by the following two procedures ?


Sub temp(ByVal Target As Range)

Application.ScreenUpdating = False

If Target.Cells.Count > 1 Then Exit Sub

If Target.Column <> 10 Then Exit Sub

If Target.Text = "FINISHED" Then

Range(Cells(Target.Row, 4), Cells(Target.Row, 16)).Copy Sheets("archief").Cells(Rows.Count, 1).End(xlUp).Offset(1)

Range(Cells(Target.Row, 3), Cells(Target.Row, 16)).ClearContents

End If

Sheets("archief").UsedRange.Replace "FINISHED", Date

Application.ScreenUpdating = True

End Sub


Public Sub test()

Call temp(Range("J18"))

End Sub


Place your cursor in the procedure test , and press F8 ; you can now step through the code of your earlier Worksheet_Change procedure. Put in the text FINISHED in cell J18 before executing the code with F8.


BTW , your procedure was missing an Application.ScreenUpdating = True statement at the end.


Narayan