adding 1 to previous cell value using code
 

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

adding 1 to previous cell value using code
 

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

adding 1 to previous cell value using code
 

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