Log in

View Full Version : Solved: Find / Replace not working - why?



Poundland
09-18-2008, 01:01 AM
Dear all,

I am trying to replace a date in a column with a blank cell, the code written below does not work, yet when I use the exact same criteria manually on the spreadsheet it works, can anybody tell me what I am doing wrong?

Your help is much appreciated.

Columns("al:al").Select
Selection.NumberFormat = "dd/mm/yyyy"
Selection.Replace What:="00/01/1900", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

shamsam1
09-18-2008, 02:31 AM
Sub ReplaceText()
Dim c As Range
For Each c In ActiveSheet.UsedRange
c = Replace(c, "00/01/1900", "")
Next
End Sub

shamsam1
09-18-2008, 02:41 AM
u can run this macro

Poundland
09-19-2008, 04:25 AM
Sub ReplaceText()
Dim c As Range
For Each c In ActiveSheet.UsedRange
c = Replace(c, "00/01/1900", "")
Next
End Sub


Sham,

Thanks for your help, although I found this this works better.

With Columns("AL:AL")
.NumberFormat = "0"
.Replace What:=0, Replacement:="", LookAt:=xlWhole
.NumberFormat = "dd/mm/yyyy"
End With