Solved: Generating Unique Number ID's

[bopo]

Hi Everyone

Basically I have a worksheet that will be storing customer records, for example there name, address, contact details etc.

However I need to link this data with orders, I have figured out that part.

The user will input data into the customer worksheet via a series of input boxes, however I'm really not sure on how to generate simple number ID's e.g. 1,2,3

I thought about looping through the ID column, adding the values that are already there, and then +1, but I would end up with loads of random numbers, which isn't what I really want.

Help appreciated

[lucas]

I thought about looping through the ID column, adding the values that are already there

If you are generating them, why is there a value already there? That statement makes a huge difference.

Is the ID column column A? Is column B always populated?

Can you post a sample of your data without any real info so we can see what you are working with?

[bopo]

Hi Lucas

Yeah I know exactly what you mean by that, I'm not generating them, I just thought of adding 1 dummny record manually with an ID of 1, therefore my addition loop idea would work.

And yeah the column A is the ID row, and data will be entered in the rest on the columns.

Hope that helps.

[lucas]

I'm not generating them, I just thought of adding 1 dummny record manually with an ID of 1

I still don't understand. What does data in the ID column look like now?

[bopo]

Okay well here's a print screen of a fake record I just typed into the worksheet

http://img440.imageshack.us/my.php?i...screen1xf3.jpg

As a finished product a user would click a button, which would prompt several input boxes which would enter data in the spreadsheet (The user is not allowed to enter data directly into the spreadsheet)

And the ID would be auto generated, and that's the bit I'm stuck on

[Bob Phillips]

Use

MAX(A:A)+1

[bopo]

I'm getting a circular reference error when I try to do that

[lucas]

Since all data to the sheet is input by inputboxes can you use a sheet change event....you would need to unprotect column A or add a line to unprotect and then reprotect after the number is added but it would probably slow things down....

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rowoffset As String
rowoffset = 0
Intersect(ActiveCell.EntireRow, Columns("A")).Value = ActiveCell.Row + rowoffset
End Sub[/VBA]

[bopo]

Thanks for the code above, I have managed to create Id's via inserting a new row, then using the ID of the row directly below and simply +1 to it.

[lucas]

I think you mean the row directly above but I get your meaning......that was my next suggestion.......mark it solved if you worked it out.

[insomnai]

Thanks for the code above, I have managed to create Id's via inserting a new row, then using the ID of the row directly below and simply +1 to it.

Hi bopo, your query is something i'm looking at myself, would you be able to explain how you came to your answer? I'm no where near your experience level here so any insight would be fantastic.

All the best

Insomnai

[bopo]

Sure

First I manually created a row, with an ID of one, next I wrote the following code to add whatever ID number is below and + 1 to it, inserted a new row, then made the new A5 equal the sum of the two values

[vba]Sub NewCustomerRecords()

Dim i, j, k As Integer

i = Sheets("CustomerDetails").Range("A5").Value
j = 1
k = i + j

Worksheets("CustomerDetails").Rows("3:3").Select
Selection.End(xlDown).Select
Selection.EntireRow.Insert

Worksheets("CustomerDetails").Range("A5").Value = k
End Sub[/vba]
Then simply created a form button, and assigned this subroutine to it.

Therefore the Ids you would create would be 1,2,3,4,5,6,7 etc

[insomnai]

Thank you very much for making that available. However, it's not entirely what I was looking for. I'm wanting to create a form with the id's there automatically starting at 1 and working onwards so no manual work is needed.... If you know what I mean.

Appreciated for your fast reply buddy!

Insomnai