Still stuck

[Adrian]

I am trying to add a new part to a list i have in Excel.
I'm currently using:
Sub NEWDETAIL()
Sheets("DETAILS").Select
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
End Sub

This takes the user to the next empty cell in column A but
i would like to go a step further. Is it possible to
continue the pattern in column A into this blank cell and
then leave the user at the bottom of column B?

I guess part of it might be something like:

Selection.AutoFill Destination:=Range("A632:A633"),
Type:=xlFillDefault

but i need it not to be relative to specific cells.
Any ideas?

 

[Frank Kabel]

Hi
I think you received some solutions in your original thread. What does
not work for you with them?

 

[Adrian]

i tried your solution but it says 'Run time error '13'.
type mismatch'

then highlights:
.Cells(lastrow, "A").Value = .Cells(lastrow -
1, "A").Value + 1

i have tried it with and without 'sub foo()' and 'End if'
but i'm not really good enough to know what i'm doing

Bobs solution ran but didnt actually continue the pattern
into the next black cell in column A, tho it did copy what
was in the last cell in column A. It just didnt do
anything with it tho.

 

[Frank Kabel]

Hi
what kind of values do you have in column A. My solution expects a
number, Bob's solution just copies the last value. So you may give some
example data for column A and your expected result after running the
macro.

In addition I would assume that also a IF formula in column A could
work

 

[Adrian]

Column A contains part numbers, ie. A0123, A0124...

Excel does recognise it as a pattern so it is possible to
click on the previous cell and copy down using the bottom
right of the cell (you know what i mean).

I want the user to be able to click the 'Add new Detail'
button and have the macro enter the next part number and
put the active cell in column B so the user can enter the
description. phew, that was a long sentance.

What i have now works fine. This is more like fine tuning
the system but it would be nice to have.

Thanks for all your help.

 

[Frank Kabel]

Hi
do your part numbers alsways have the layout A0000
if yes change the line
..Cells(lastrow, "A").Value = .Cells(lastrow - 1, "A").Value + 1

to the following line (all in one line)
..Cells(lastrow, "A").Value = "A" & Format(--right(.Cells(lastrow - 1,
"A").Value,4) + 1,"0000")