Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
Can I replace a ' at the beginning of a text cell using Replace | Excel Discussion (Misc queries) | |||
How to replace a date, change May to June keeping numerical dates | Excel Discussion (Misc queries) | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
How do I replace dates earlier than certain date? | Excel Discussion (Misc queries) |