Increment using last row and first cell

[cainey1991]

Hi all,

I am looking for a simple function. I have managed to break it down into small chunks but not getting anywhere with it.

When the macro is run it looks at a table and increments the ID when it adds a row.

ID	name
D-1	Bill
D-2	Ben

<tbody>
</tbody>

I can add the row but cant increment the ID. I am trying to break it down :
1. Look at last value in the last row and first cell - cannot work out
2. Increment this value by 1

Any help would be great.

D

 

[Michael M]

MAybe this

Excel 2010
	A	B
1	ID	name
2	D-1	Bill
3	D-2	Ben
4	D -3
5	D -4
Sheet1

Cell Formulas
Range	Formula
A4	="D -" & RIGHT(A3,1)+1
A5	="D -" & RIGHT(A4,1)+1

 

[Michael M]

Better

Excel 2010
	A	B
1	ID	name
2	D-1	Bill
3	D-2	Ben
4	D -3
5	D -4
6	D -5
Sheet1

Cell Formulas
Range	Formula
A4	="D -"& ABS(MID(A3,FIND("-",A3,1),99))+1
A5	="D -"& ABS(MID(A4,FIND("-",A4,1),99))+1
A6	="D -"& ABS(MID(A5,FIND("-",A5,1),99))+1

 

[cainey1991]

Does that work in VBA also?

Thanks for the response.

D

 

[Michael M]

It can be applied to VBA
Try

Private Sub worksheet_change(ByVal target As Range)
lr = Cells(Rows.Count, "B").End(xlUp).Row
If target.Count > 1 Then Exit Sub
If Intersect(target, Range("B2:B" & lr)) Is Nothing Then Exit Sub
x = 1
For r = 2 To lr
    Range("A" & r).Value = "D- " & x
    x = x + 1
Next r
End Sub