macro to replace
 

Hi, in column "A" i have dates. (01.12.2010 ; 02.12.2010 ; 03.12.2010....) I
need to replace only the year.

01.12.2010 01.12.2009
02.12.2010 02.12.2009
03.12.2010 02.12.2009
.......

Can a macro replace only the year?
Thanks!

macro to replace
 

--Use Find/Replace dialog to replace 2010 with 2009

--OR use a formula in cell B1 and copy down
=DATE(2009,MONTH(A1),DAY(A1))

--OR if you are looking for a macro then try

Sub Macro()
Dim cell As Range
For Each cell In Selection
cell.Value = DateAdd("yyyy", -1, cell)
Next
End Sub

--
Jacob


"puiuluipui" wrote:

Hi, in column "A" i have dates. (01.12.2010 ; 02.12.2010 ; 03.12.2010....) I
need to replace only the year.

01.12.2010 01.12.2009
02.12.2010 02.12.2009
03.12.2010 02.12.2009
......

Can a macro replace only the year?
Thanks!

macro to replace
 

Hi Jacob, it's working, but i have to select all cells with date in column A.
Can a macro do this? I need a macro to select in column A, all cells with
date from A2 to tha last cell with date.
Can this be done?
Thanks

"Jacob Skaria" a scris:

--Use Find/Replace dialog to replace 2010 with 2009

--OR use a formula in cell B1 and copy down
=DATE(2009,MONTH(A1),DAY(A1))

--OR if you are looking for a macro then try

Sub Macro()
Dim cell As Range
For Each cell In Selection
cell.Value = DateAdd("yyyy", -1, cell)
Next
End Sub

--
Jacob


"puiuluipui" wrote:

Hi, in column "A" i have dates. (01.12.2010 ; 02.12.2010 ; 03.12.2010....) I
need to replace only the year.

01.12.2010 01.12.2009
02.12.2010 02.12.2009
03.12.2010 02.12.2009
......

Can a macro replace only the year?
Thanks!

macro to replace
 

Try

Dim cell As Range, rngData As Range

Set rngData = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cell In rngData
If IsDate(cell) Then cell.Value = DateAdd("yyyy", -1, cell)
Next

--
Jacob


"puiuluipui" wrote:

Hi Jacob, it's working, but i have to select all cells with date in column A.
Can a macro do this? I need a macro to select in column A, all cells with
date from A2 to tha last cell with date.
Can this be done?
Thanks

"Jacob Skaria" a scris:

--Use Find/Replace dialog to replace 2010 with 2009

--OR use a formula in cell B1 and copy down
=DATE(2009,MONTH(A1),DAY(A1))

--OR if you are looking for a macro then try

Sub Macro()
Dim cell As Range
For Each cell In Selection
cell.Value = DateAdd("yyyy", -1, cell)
Next
End Sub

--
Jacob


"puiuluipui" wrote:

Hi, in column "A" i have dates. (01.12.2010 ; 02.12.2010 ; 03.12.2010....) I
need to replace only the year.

01.12.2010 01.12.2009
02.12.2010 02.12.2009
03.12.2010 02.12.2009
......

Can a macro replace only the year?
Thanks!

macro to replace
 

It's perfect!
Thanks allot!

"Jacob Skaria" a scris:

Try

Dim cell As Range, rngData As Range

Set rngData = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cell In rngData
If IsDate(cell) Then cell.Value = DateAdd("yyyy", -1, cell)
Next

--
Jacob


"puiuluipui" wrote:

Hi Jacob, it's working, but i have to select all cells with date in column A.
Can a macro do this? I need a macro to select in column A, all cells with
date from A2 to tha last cell with date.
Can this be done?
Thanks

"Jacob Skaria" a scris:

--Use Find/Replace dialog to replace 2010 with 2009

--OR use a formula in cell B1 and copy down
=DATE(2009,MONTH(A1),DAY(A1))

--OR if you are looking for a macro then try

Sub Macro()
Dim cell As Range
For Each cell In Selection
cell.Value = DateAdd("yyyy", -1, cell)
Next
End Sub

--
Jacob


"puiuluipui" wrote:

Hi, in column "A" i have dates. (01.12.2010 ; 02.12.2010 ; 03.12.2010....) I
need to replace only the year.

01.12.2010 01.12.2009
02.12.2010 02.12.2009
03.12.2010 02.12.2009
......

Can a macro replace only the year?
Thanks!