Dates in Excel
 

Hi,

i have a worksheet, where Column A is formatted for mmm-yy
What i would like to be able to do is to add a row at R3 and have Column A
automatically increment to the next month. Eg - if A3 - Aug-07 and I insert
a new Row 3, this will have an A3 of Sept-07 and all other rows will move
one row down?

Is this possible please

thanks
A

Dates in Excel
 

Alexey,

There is no way that I know of of capturing the inserting of a row. The
onlything that I can suggest it to have an event macro run when you fill in
the last cell - say K3.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ActionCell As String

ActionCell = Target.Address
If Not ActionCell = "$K$3" Then
Exit Sub
End If
Debug.Print "S"
Rows("3:3").Insert Shift:=xlDown
Range("A3").FormulaR1C1 =
"=DATE(YEAR(R[+1]C),MONTH(R[+1]C)+1,DAY(R[+1]C))"
End Sub

Post bck if you need more help.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alexey" wrote in message
...
Hi,

i have a worksheet, where Column A is formatted for mmm-yy
What i would like to be able to do is to add a row at R3 and have Column A
automatically increment to the next month. Eg - if A3 - Aug-07 and I
insert a new Row 3, this will have an A3 of Sept-07 and all other rows
will move one row down?

Is this possible please

thanks
A

Dates in Excel
 

Thanks Sandy

Alex

"Sandy Mann" wrote in message
...
Alexey,

There is no way that I know of of capturing the inserting of a row. The
onlything that I can suggest it to have an event macro run when you fill
in
the last cell - say K3.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ActionCell As String

ActionCell = Target.Address
If Not ActionCell = "$K$3" Then
Exit Sub
End If
Debug.Print "S"
Rows("3:3").Insert Shift:=xlDown
Range("A3").FormulaR1C1 =
"=DATE(YEAR(R[+1]C),MONTH(R[+1]C)+1,DAY(R[+1]C))"
End Sub

Post bck if you need more help.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alexey" wrote in message
...
Hi,

i have a worksheet, where Column A is formatted for mmm-yy
What i would like to be able to do is to add a row at R3 and have Column
A
automatically increment to the next month. Eg - if A3 - Aug-07 and I
insert a new Row 3, this will have an A3 of Sept-07 and all other rows
will move one row down?

Is this possible please

thanks
A

Dates in Excel
 

You're very welcome, thnks for posting back.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alexey" wrote in message
...
Thanks Sandy

Alex

"Sandy Mann" wrote in message
...
Alexey,

There is no way that I know of of capturing the inserting of a row. The
onlything that I can suggest it to have an event macro run when you fill
in
the last cell - say K3.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ActionCell As String

ActionCell = Target.Address
If Not ActionCell = "$K$3" Then
Exit Sub
End If
Debug.Print "S"
Rows("3:3").Insert Shift:=xlDown
Range("A3").FormulaR1C1 =
"=DATE(YEAR(R[+1]C),MONTH(R[+1]C)+1,DAY(R[+1]C))"
End Sub

Post bck if you need more help.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alexey" wrote in message
...
Hi,

i have a worksheet, where Column A is formatted for mmm-yy
What i would like to be able to do is to add a row at R3 and have Column
A
automatically increment to the next month. Eg - if A3 - Aug-07 and I
insert a new Row 3, this will have an A3 of Sept-07 and all other rows
will move one row down?

Is this possible please

thanks
A