Dates in Excel

[Alexey]

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

 

[Sandy Mann]

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk

 

[Alexey]

Thanks Sandy

Alex

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk

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

 

[Sandy Mann]

You're very welcome, thnks for posting back.

--
Regards,

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk

Thanks Sandy

Alex

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk

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