Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding one to the number from the previous cell with text... | Excel Discussion (Misc queries) | |||
Adding calculated value to previous cell | Excel Discussion (Misc queries) | |||
How can I copy a value from a cell and paste it into another cell while adding it to the previous value in that cell | Excel Worksheet Functions | |||
adding a code to a cell? | Excel Programming | |||
adding a code to a cell? | Excel Programming |