Macro Not Changing Date Values

[clist-1]

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?

Thanks for any help.

 

[lhkittle]

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?



Thanks for any help.

Here's what I "think" is wrong, but I don't have a solution. The date is really a serial number like 41280.00. Your macro is trying to 'pick' the month out
03 out of the serial number and replace with a new month of 04. Maybe adding 30to the serial number would bring you into the 04 month with a new serial number like 41310.00.

Sorry for not having a solution.

Regards,
Howard

 

[Claus Busch]

Hi,

Am Sun, 6 Jan 2013 05:14:09 -0800 (PST) schrieb (e-mail address removed):

Range("A4:A33").Select
Selection.Replace What:="/03/", Replacement:="/04/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,

try:

Sub ChangeDate()
Dim rngC As Range

For Each rngC In Range("A4:A33")
rngC = DateSerial(Year(rngC), Month(rngC) + 1, Day(rngC))
Next
End Sub


Regards
Claus Busch

 

[Claus Busch]

Hi,

Am Sun, 6 Jan 2013 14:52:54 +0100 schrieb Claus Busch:

For Each rngC In Range("A4:A33")
rngC = DateSerial(Year(rngC), Month(rngC) + 1, Day(rngC))
Next

because of different days per month:

Sub ChangeDate()
Dim rngC As Range

[A4] = DateSerial(Year([A4]), Month([A4]) + 1, Day([A4]))
For Each rngC In Range("A5:A34")
rngC = rngC.Offset(-1, 0) + 1
Next
End Sub

Or write the first of month in A4. In A5:
=A4+1 and copy it down. Then you only have to change the date in A4


Regards
Claus Busch

 

[joeu2004]

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.

 

[GS]

joeu2004 pretended :

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.

I agree. I use Double for the same reason!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi again,

Sub ChangeMonth()
Dim First As Double
Dim Last As Double

First = DateSerial(Year([A4]), Month([A4]) + 1, 1)
Last = DateSerial(Year(First), Month(First) + 1, 0)
Range("A4:A34").Clear
Range("A4") = First
Range("A4").DataSeries Rowcol:=xlColumns, Type:=xlChronological, _
Date:=xlDay, Step:=1, Stop:=Last, Trend:=False
Range("A4:A34").NumberFormat = "yyyy-MMM-dd"
End Sub


Regards
Claus Busch

 

[clist-1]

Hi again,



Sub ChangeMonth()

Dim First As Double

Dim Last As Double



First = DateSerial(Year([A4]), Month([A4]) + 1, 1)

Last = DateSerial(Year(First), Month(First) + 1, 0)

Range("A4:A34").Clear

Range("A4") = First

Range("A4").DataSeries Rowcol:=xlColumns, Type:=xlChronological, _

Date:=xlDay, Step:=1, Stop:=Last, Trend:=False

Range("A4:A34").NumberFormat = "yyyy-MMM-dd"

End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks to all for your replies. My Short Date format is yyyy/mm/dd, so I do not think that's a problem.

I hadn't mentioned this piece of information, which is very important to the coding solution: The spreadsheet has the dates in descending order, withan Average line preceding each month. The four-line solution of Mr. Buschlooks like it will work for me.

I had experimented with having one macro do all twelve months, but decided against it (I may try again to do that).

Thanks again.