VBA Replace Dates
 

Hi

I have an Excel worksheet which pulls data from SQL Server which treat null
dates as 01/01/1900

I'm trying to replace all such dates with blanks on the worksheet

In Excel when I go Edit Replace and enter Find What = 01/01/1900 leaving
Replace blank it works marvellously

However in VBA the following does not work (even though this was from
recording the above action)

Cells.Replace What:="01/01/1900", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

I have tried also using numerics for the What:= again with no luck

Am I being stupid?

Thanks

Matt

VBA Replace Dates
 

Restrict your search range, then try searching for zero

Equally (but slower):

Dim cel as Range, rngNullDateRange as range
For Each cel in rngNullDateRange
if cel.value=0
cel.clearcontents
end if
next

of
For Each cel in rngNullDateRange
if cel.text="01/01/1900"
cel.clearcontents
end if
next


--
HTH
Roger
Shaftesbury (UK)




"Matt Connor" wrote in message
...
Hi

I have an Excel worksheet which pulls data from SQL Server which treat
null
dates as 01/01/1900

I'm trying to replace all such dates with blanks on the worksheet

In Excel when I go Edit Replace and enter Find What = 01/01/1900
leaving
Replace blank it works marvellously

However in VBA the following does not work (even though this was from
recording the above action)

Cells.Replace What:="01/01/1900", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

I have tried also using numerics for the What:= again with no luck

Am I being stupid?

Thanks

Matt

VBA Replace Dates
 

Hi Matt,

I have an Excel worksheet which pulls data from SQL Server which treat
null
dates as 01/01/1900

I'm trying to replace all such dates with blanks on the worksheet


if you have the chance try to solve the problem where the data is coming
from, eg. change the sql-statement to
IIF(mydate = 1/1/1900, NULL, mydate).

For me, changing the numberformat to standard and then replacing the value 1
with nothing worked, eg:
Range("B2:B9").Select
Selection.NumberFormat = "General"
Range("B7:B8").Select
Selection.Replace What:="1", Replacement:="test", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

arno