Macro For Unique Sequential Numbering on multiple sheets

[dkannapel]

Hello,

I fear the answer to this may be easy, but I have yet to find a solution that works so I am hoping someone can help me out. I have a workbook that contains 4 worksheets that each have identical column headings. I also have a userform that inputs data into each of these worksheets depending on which category is selected on the userform. Where there is a difference is in their numbering structure. Sheet 1 is 1,2,3,4,5,etc. Sheet 2 is A1,A2,A3,etc. Sheet 3 is N1,N2,N3,etc and so on. What I would like to do is have the enteries auto number as the data is entered into them via the userform. The data from the userform is entered into columns B-K and the number of the entry is in column A. Currently I am using the dragdown feature to autonumber them once they are entered on the form. Any help here would be much appreciated.

Thanks

[HaHoBe]

Hi, dkannapel,

use a custom format for the cells in COlumn A of the sheets, for Sheet3 this may look like

"N"#

The code to add a new unique number to Sheet3 may look like

With Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)
  .Offset(1, 0).Value = WorksheetFunction.Max(Range("a2", .Address)) + 1
End With

Ciao,
Holger

[dkannapel]

Holger,

Thank you for the quick reply. The code you provided almost works perfectly. What I did find, however is that once the userform was closed and reopened the numbering starts again at 1 (i.e if the last entry was numbered A5 when the userform was closed, the next entry once the userform was opened and data entered was numbered A1). What I did find that has worked, mostly, is a combination of what you suggested and another bit of code I had that was part of my earlier attempts:

I used the custom formatting for Column A of all the worksheets like you suggested. I then used the code:

Set rng = Cells(Rows.Count, 1).End(xlUp)
rng.Offset(1) = rng + 1

This works great on all the worksheets but one, which has me really confused as to why. All of the worksheets are formatted the same way (except column A which has the custom numbering format) yet when I try to enter the data into Sheet6 it returns a 'Type Mismatch' error at the "rng.offset" line of code. I was hoping you could possibly point me in the direction to try to debug it. Everything I have tried so far has not worked. Thank you in advance for your help.

[HaHoBe]

Hi, dkannapel,

this is one of the rare occasions where I would like to have a look at the workbook as I have no clue right now what may be going wrong.

Ciao,
Holger

[dkannapel]

Holger,

Here is a copy of the workbook. All of the data is just jibberish at this point as I go through with testing the different additions I make. Again, I really appreciate your help on this.



Thanks

[HaHoBe]

Hi, dkannapel,

if Iīm correct Excel has some difficulties in finding a Max value for Strings as A2 to A4 are String with the K being part of the cell instead of being part of the formatting. So if you run the code with Near Miss the forst available number is 1 as the Max-Function wonīt work with Strings and would return 0.

Ciao,
Holger

[dkannapel]

Holger,

Maybe I'm missing something here, but then aren't the other values is sheets JSA and AI strings as well as they are A1 and J1? If they are then they should have the same problem as the near miss sheet, no? What confuses me is they dont, they work just fine. It is only the near miss sheet.

[HaHoBe]

Hi, dkannapel,

donīt be irritated by whatīs diplayed in the cell. Press F2 and see that itīs only a number which is displayed there which will serve the Max.

Ciao,
Holger

[jindon]

Try change to

'Add 1 to CAPA Number
With Cells(Rows.Count, 1).End(xlUp)
    .AutoFill .Resize(2), xlFillSeries
End With

'The the Controls for the Next Entry