I've recorded a macro to insert lines, copy values, and change the
month within the copied values, but if I run the macro to test, the
month changes don't occur. They do occur properly when I record the
macro.
An example of a copied value is 2013/03/30 and it is formatted as
Custom yyyy-mmm-dd. Here's the part from the macro to do the replace:
Range("A4:A33").Select
Selection.Replace What:="/03/", Replacement:="/04/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Any suggestions to get this to work properly?
Others are correct: true Excel dates are stored as integer "serial
numbers", not as the strings that we might see due to formatting.
Nonetheless, we cannot escape the fact that you say it worked when you did
this manually. And it does!
So indeed, Excel find-and-replace will replace /03/ with /04/ in a cell that
contains a date serial number.
Moreover, the recorded macro __does__ work for me.
However, all of this depends on the short-date form defined in the Regional
and Language Options control panel.
For example, if the short-date form is d/M/yyyy, What:="/03/" does not work.
We must write What:="/3/" instead.
So I wonder if your recorded macro is failing because the short-date form is
d/M/yyyy instead of d/MM/yyyy in the environment where it is failing.
In any case, because of that external configuration dependency and
sensitivity, I think this method is ill-advised.
It is also ill-advised for the reason that Claus mentioned: not all months
have the same number of days. Consequently, the find-and-replace might have
a __text__ result because the "date" is not recognized as such by Excel.
That will cause other problems.
So it would be more reliable to write something like the following....
Sub doit()
Const oldMon As Long = 8
Const newMon As Long = 9
Dim c As Variant, x As Double
For Each c In Range("A4:A33")
If Month(c) = oldMon Then
x = DateSerial(Year(c), newMon, Day(c))
If Month(x) <> newMon Then
' Day(c) exceeds end of newMon
' set Day(x) to end of newMon
x = DateSerial(Year(c), newMon + 1, 0)
End If
c.Value = x
End If
Next c
End Sub
Note: Some people might prefer Dim x As Date instead of Double. Both
should be fine. I prefer Dim x As Double because VBA's special
interpretation of Date variables sometimes trips me up.