Hi,
In 'B', I would like the next empty cell to be automatically filled using
the data from the cell above and adding 1.
For instance, when text is entered somewhere in column 'C', in the
corresponding cell in 'B' I'd like "07/***" to show where "07/" is formatted
(to show the year) and "***" would be the next sequential number after the
cell above it.

I'd like it incorporated into this code which adds date to 'A':

Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C6:C3000")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, -1).Value = ***cell above plus 1***
Application.EnableEvents = True
.Offset(0, -2).Value = Format(Date, "dd/mmm/yy")
End If

Thanks for your thoughts
--
Traa Dy Liooar

Jock

You don't need ANY VBA to do this
Format the column as "07/"General
put this formula in A2, for example:
=MAX($A$1:A1)+1
If you're in cell A10, this formula would read =MAX($A$1:A9)+1.
Wherever this formula appears, it'll be one more than the previous entry!
Bob Umlas
Excel MVP

"Jock" wrote:

> Hi,
> In 'B', I would like the next empty cell to be automatically filled using
> the data from the cell above and adding 1.
> For instance, when text is entered somewhere in column 'C', in the
> corresponding cell in 'B' I'd like "07/***" to show where "07/" is formatted
> (to show the year) and "***" would be the next sequential number after the
> cell above it.
>
> I'd like it incorporated into this code which adds date to 'A':
>
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range("C6:C3000")) Is Nothing Then
> With Target
> If .Value <> "" Then
> .Offset(0, -1).Value = ***cell above plus 1***
> Application.EnableEvents = True
> .Offset(0, -2).Value = Format(Date, "dd/mmm/yy")
> End If
>
> Thanks for your thoughts
> --
> Traa Dy Liooar
>
> Jock

Thanks Bob. Had to adapt it as I only require next number to appear when
there was text in 'C'

Cheers
--
Traa Dy Liooar

Jock


"Bob Umlas, Excel MVP" wrote:

> You don't need ANY VBA to do this
> Format the column as "07/"General
> put this formula in A2, for example:
> =MAX($A$1:A1)+1
> If you're in cell A10, this formula would read =MAX($A$1:A9)+1.
> Wherever this formula appears, it'll be one more than the previous entry!
> Bob Umlas
> Excel MVP
>
> "Jock" wrote:
>
> > Hi,
> > In 'B', I would like the next empty cell to be automatically filled using
> > the data from the cell above and adding 1.
> > For instance, when text is entered somewhere in column 'C', in the
> > corresponding cell in 'B' I'd like "07/***" to show where "07/" is formatted
> > (to show the year) and "***" would be the next sequential number after the
> > cell above it.
> >
> > I'd like it incorporated into this code which adds date to 'A':
> >
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range("C6:C3000")) Is Nothing Then
> > With Target
> > If .Value <> "" Then
> > .Offset(0, -1).Value = ***cell above plus 1***
> > Application.EnableEvents = True
> > .Offset(0, -2).Value = Format(Date, "dd/mmm/yy")
> > End If
> >
> > Thanks for your thoughts
> > --
> > Traa Dy Liooar
> >
> > Jock