Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck
Hi
I think you received some solutions in your original thread. What does not work for you with them? -- Regards Frank Kabel Frankfurt, Germany "Adrian" schrieb im Newsbeitrag ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck
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 -- Regards Frank Kabel Frankfurt, Germany "Adrian" schrieb im Newsbeitrag ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck
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") -- Regards Frank Kabel Frankfurt, Germany "Adrian" schrieb im Newsbeitrag ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorry but i am stuck again | New Users to Excel | |||
Im stuck again... | Excel Discussion (Misc queries) | |||
Stuck... | Excel Discussion (Misc queries) | |||
stuck | Excel Worksheet Functions | |||
Stuck with an =IF | Excel Worksheet Functions |