ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Macro to Find and Replace (https://www.excelbanter.com/excel-discussion-misc-queries/121602-macro-find-replace.html)

R Storey
Macro to Find and Replace
 
I have recorded a macro to find and replace all the dates but when I run it
it doesn't actually change them. I want to change all the dates so that they
are all the 1st of the the month instead of specifying the day. I can't think
of an easier way of doing it so if anyone has any better ideas it'll be very
appreciated!!!

Here's the code:

Columns("U:U").Select
Selection.Replace What:="??/12/2005", Replacement:="01/12/2005",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/01/2006", Replacement:="01/01/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/02/2006", Replacement:="01/02/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/03/2006", Replacement:="01/03/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

It doesn't come up with an error, it just doesn't do it!

Thanks in advance!!!!

Bernie Deitrick
Macro to Find and Replace
 
Insert a new column, and use a formula like

=DATE(YEAR(U2),MONTH(U2),1)

Copy that formula down to match your dates (you may need some error checking with that), then copy
and pastespecial values over your original dates, then delete your new column. Can be done with a
macro as well...

HTH,
Bernie
MS Excel MVP


"R Storey" wrote in message
...
I have recorded a macro to find and replace all the dates but when I run it
it doesn't actually change them. I want to change all the dates so that they
are all the 1st of the the month instead of specifying the day. I can't think
of an easier way of doing it so if anyone has any better ideas it'll be very
appreciated!!!

Here's the code:

Columns("U:U").Select
Selection.Replace What:="??/12/2005", Replacement:="01/12/2005",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/01/2006", Replacement:="01/01/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/02/2006", Replacement:="01/02/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/03/2006", Replacement:="01/03/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

It doesn't come up with an error, it just doesn't do it!

Thanks in advance!!!!



Dave Peterson
Macro to Find and Replace
 
I would insert a helper column and use a formula like:

=U1-DAY(U1)+1
drag it down the column
Copy and paste special|values over column U and delete that helper column.

R Storey wrote:

I have recorded a macro to find and replace all the dates but when I run it
it doesn't actually change them. I want to change all the dates so that they
are all the 1st of the the month instead of specifying the day. I can't think
of an easier way of doing it so if anyone has any better ideas it'll be very
appreciated!!!

Here's the code:

Columns("U:U").Select
Selection.Replace What:="??/12/2005", Replacement:="01/12/2005",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/01/2006", Replacement:="01/01/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/02/2006", Replacement:="01/02/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/03/2006", Replacement:="01/03/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

It doesn't come up with an error, it just doesn't do it!

Thanks in advance!!!!

--

Dave Peterson

R Storey
Macro to Find and Replace
 
Thanks to both of you, both options work fantastically!!!

"Dave Peterson" wrote:

I would insert a helper column and use a formula like:

=U1-DAY(U1)+1
drag it down the column
Copy and paste special|values over column U and delete that helper column.

R Storey wrote:

I have recorded a macro to find and replace all the dates but when I run it
it doesn't actually change them. I want to change all the dates so that they
are all the 1st of the the month instead of specifying the day. I can't think
of an easier way of doing it so if anyone has any better ideas it'll be very
appreciated!!!

Here's the code:

Columns("U:U").Select
Selection.Replace What:="??/12/2005", Replacement:="01/12/2005",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/01/2006", Replacement:="01/01/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/02/2006", Replacement:="01/02/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/03/2006", Replacement:="01/03/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

It doesn't come up with an error, it just doesn't do it!

Thanks in advance!!!!

--

Dave Peterson

CLR
Macro to Find and Replace
 
A Macro approach, assuming your dates in column A..........

Sub ChangeDayTo1stOfMonth()
'======================================
'Changes all dates in column A to the first day of the month
'======================================
Dim lastrow As Long, r As Long
Dim num1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
For r = lastrow To 1 Step -1
If Cells(r, "A") 0 Then
Cells(r, "A").Select
num1 = Selection.Value
End If
num1 = Format(num1, "m/1/yyyy")
With ActiveCell
.Value = num1
End With
Next r
End Sub

Vaya con Dios,
Chuck, CABGx3




"R Storey" wrote:

I have recorded a macro to find and replace all the dates but when I run it
it doesn't actually change them. I want to change all the dates so that they
are all the 1st of the the month instead of specifying the day. I can't think
of an easier way of doing it so if anyone has any better ideas it'll be very
appreciated!!!

Here's the code:

Columns("U:U").Select
Selection.Replace What:="??/12/2005", Replacement:="01/12/2005",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/01/2006", Replacement:="01/01/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/02/2006", Replacement:="01/02/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/03/2006", Replacement:="01/03/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

It doesn't come up with an error, it just doesn't do it!

Thanks in advance!!!!

Dave Peterson
Macro to Find and Replace
 
I think that this could be dangerous.

num1 = Format(num1, "m/1/yyyy")

It relies on excel parsing that entry. If your date settings are mdy, then no
problem. But if your settings are dmy, you may not get the date you want--and
if your settings are ymd, you may not even get a date.

I think I'd use:
num1 = dateserial(year(num1),month(num1),1)

And apply a nice unambiguous .numberformat to that range so that I could verify.



CLR wrote:

A Macro approach, assuming your dates in column A..........

Sub ChangeDayTo1stOfMonth()
'======================================
'Changes all dates in column A to the first day of the month
'======================================
Dim lastrow As Long, r As Long
Dim num1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
For r = lastrow To 1 Step -1
If Cells(r, "A") 0 Then
Cells(r, "A").Select
num1 = Selection.Value
End If
num1 = Format(num1, "m/1/yyyy")
With ActiveCell
.Value = num1
End With
Next r
End Sub

Vaya con Dios,
Chuck, CABGx3

"R Storey" wrote:

I have recorded a macro to find and replace all the dates but when I run it
it doesn't actually change them. I want to change all the dates so that they
are all the 1st of the the month instead of specifying the day. I can't think
of an easier way of doing it so if anyone has any better ideas it'll be very
appreciated!!!

Here's the code:

Columns("U:U").Select
Selection.Replace What:="??/12/2005", Replacement:="01/12/2005",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/01/2006", Replacement:="01/01/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/02/2006", Replacement:="01/02/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/03/2006", Replacement:="01/03/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

It doesn't come up with an error, it just doesn't do it!

Thanks in advance!!!!

--

Dave Peterson

CLR
Macro to Find and Replace
 
Point well taken Dave.........I just used MY format (figuring everybody else
does the same) and failed to observe the OP's format........

thanks for the head's up

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote:

I think that this could be dangerous.

num1 = Format(num1, "m/1/yyyy")

It relies on excel parsing that entry. If your date settings are mdy, then no
problem. But if your settings are dmy, you may not get the date you want--and
if your settings are ymd, you may not even get a date.

I think I'd use:
num1 = dateserial(year(num1),month(num1),1)

And apply a nice unambiguous .numberformat to that range so that I could verify.



CLR wrote:

A Macro approach, assuming your dates in column A..........

Sub ChangeDayTo1stOfMonth()
'======================================
'Changes all dates in column A to the first day of the month
'======================================
Dim lastrow As Long, r As Long
Dim num1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
For r = lastrow To 1 Step -1
If Cells(r, "A") 0 Then
Cells(r, "A").Select
num1 = Selection.Value
End If
num1 = Format(num1, "m/1/yyyy")
With ActiveCell
.Value = num1
End With
Next r
End Sub

Vaya con Dios,
Chuck, CABGx3

"R Storey" wrote:

I have recorded a macro to find and replace all the dates but when I run it
it doesn't actually change them. I want to change all the dates so that they
are all the 1st of the the month instead of specifying the day. I can't think
of an easier way of doing it so if anyone has any better ideas it'll be very
appreciated!!!

Here's the code:

Columns("U:U").Select
Selection.Replace What:="??/12/2005", Replacement:="01/12/2005",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/01/2006", Replacement:="01/01/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/02/2006", Replacement:="01/02/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/03/2006", Replacement:="01/03/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

It doesn't come up with an error, it just doesn't do it!

Thanks in advance!!!!

--

Dave Peterson


All times are GMT +1. The time now is 07:04 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com