View Full Version : Solved: Generating Unique Number ID's
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
01-10-2009, 11:00 AM
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?
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
01-10-2009, 11:26 AM
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?
Okay well here's a print screen of a fake record I just typed into the worksheet
http://img440.imageshack.us/my.php?image=printscreen1xf3.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
01-10-2009, 11:45 AM
Use
MAX(A:A)+1
I'm getting a circular reference error when I try to do that :(
lucas
01-10-2009, 12:10 PM
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....
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
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
01-10-2009, 12:19 PM
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
01-11-2009, 07:04 AM
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
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
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
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
01-11-2009, 07:30 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.