replace 29 february with 28 february with vba

  • Thread starter Thread starter Herman
  • Start date Start date
H

Herman

I have a column with dates (dd/mm/yyyy) in which I want to replace
every 29/02 with 28/02.
It works perfectly with the regular "Replace" menu item. Recording
that succesfull process generated the following code :

Selection.Replace What:="29/02", Replacement:="28/02",
LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

However, executing this very same code gives no result.
Thanks very much for any help here.
Herman
 
Herman laid this down on his screen :
I have a column with dates (dd/mm/yyyy) in which I want to replace
every 29/02 with 28/02.
It works perfectly with the regular "Replace" menu item. Recording
that succesfull process generated the following code :

Selection.Replace What:="29/02", Replacement:="28/02",
LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

However, executing this very same code gives no result.
Thanks very much for any help here.
Herman
Click to expand...

Could the reason be that you've already done this when you recorded the
macro?
 
Herman laid this down on his screen :




Could the reason be that you've already done this when you recorded the
macro?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

No, Garry. Of course I saw to it that there were some 29/02 dates in
the test range.
Actually I tried it all on a test range A1:A20.
Thanks anyway.
Herman
 
Can try somthing along the lines:
dim oneCell as range

for each oneCell in Selection
if day(oneCell.value2)=29 and month(oneCell.value2)=2 then
onecell.value=date(year(oneCell.value2),2,28)
end if
netx oneCell
 
I have a column with dates (dd/mm/yyyy) in which I want
to replace every 29/02 with 28/02.
It works perfectly with the regular "Replace" menu item.
 Recording that succesfull process generated the following
code :
Selection.Replace What:="29/02", Replacement:="28/02",
LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False,
SearchFormat:=False, _
        ReplaceFormat:=False

However, executing this very same code gives no result.
Click to expand...

I presume that "no result", you mean "no change".

I suspect that the root cause of the problem is: some of your dates
are interpreted as text, others as numeric dates.

For example, 29/02/2011 will be interpreted as text, regardless of the
cell format, because there is no Feb 29 in 2011. However, 29/02/2012
will be interpreted as a numeric date (even if it is text!) because
there is Feb 29 in 2012.

Ironically, your Find/Replace method works fine for the (bogus)
textual dates, but not for the (correct) numeric dates.

Try:

Sub doit()
Dim c As Range
For Each c In Selection
If Left(c.Text, 6) = "29/02/" Then
c = "2/28" & Mid(c.Text, 6, Len(c.Text))
End If
Next
End Sub
 
Back
Top