Create an incremental unique ID

[Scooby]

Hi

Can anyone please help me with some code? I'm very new to VBA

I have set up a user form for data entry of customer data. Each time a customer record is entered, I would like the Customer ID to increase by 1 to create a unique ID ie the first row of data is 101, the second 102, the third 103 etc.

Rather than do this on the data capture page, I would like to show the increasing ID in the user form so the user who are entering the data can keep track of how many records they have entered.

I have got as far as activating the data sheet, finding the next row and putting the customer number in it, but I'm not sure how to generate this number rather than create it each time.

Any help would be great - thank you!

'Make sure Data is active
Sheets("Data").Activate

'Determine the next empty row
NextRow = Application.WorksheetFunction. _
CountA(Range("A:A")) + 1

'Transfer the Customer Number
Cells(NextRow, 1) = TextCustomerNumber.Value

 

[Deepak]

Hi

Welcome to the board...

Can we have the your data model so that we can loop with a decent way.

 

[Scooby]

Hi

Welcome to the board...

Can we have the your data model so that we can loop with a decent way.

Hi!

Really sorry but I'm not quite sure what you mean by data model? Do you mean the spreadsheet in which I collect the data?

 

[ThauThème]

Hi Scooby,

Try this code :

Dim D As Worksheet
Dim CEL As Range

Set D = Sheets("Data")
Set CEL = IIf(D.Range("A1").Value = "", D.Range("A1"), D.Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0))
CEL.Value = TextCustomerNumber.Value

 

[Deepak]

Hi!

Really sorry but I'm not quite sure what you mean by data model? Do you mean the spreadsheet in which I collect the data?

Yes as sample file.