Generating new ID VBA code

[DT909]

Hi guys,

I'm trying to create a userform where I need a textbox generation new ID number which is based on information coming from 2 different Combobox, each one with 3 letters and numbers + our fiscal year.

In each Combobox, there're 7 to 18 possible choices.

At the end the ID should like : CITP112018001 and the next CITP112018002...
If the first 10 digit changes like UCFP052017 then the 3 last digit start again 001, 002 etc.


My code works well when it comes to add or increase the last 3 digits. But when I have a new prefix (the first 10) it doesn't add the 001.

Here's the code :


Sub findnextnumber()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

Dim lr As Long
Dim x As Long


If Me.PCList = "" Or Me.proFITList = "" Then
Exit Sub
End If


a = Left(Me.PCList, 3)
b = Left(Me.proFITList, 3)

If Date > 9 / 30 / Year(Date) Or Date < 10 / 1 / Year(Date) + 1 Then

C = Year(Date) + 1

Else
C = Year(Date)
End If



myName = UCase(a & b & C)

lr = ws.Cells(Rows.Count, 3).End(xlUp).Row

For x = 1 To lr
If Left(ws.Cells(x, 3), 13) = myName Then

'find last number that applies
lastnum = Right(ws.Cells(x, 3), 3) + 1

End If

lastnum = Format(lastnum, "00#")
Me.EPCNum = myName & lastnum

Next x

End Sub

Any suggestion ?

[p45cal]

Try this (untested). It will fail if the last 3 numbers for a given 10-character prefix are not in ascending order in the sheet:

Sub findnextnumber()
Dim ws As Worksheet
Dim lr As Long
Dim x As Long

Set ws = ThisWorkbook.Sheets("Sheet1")
If Me.PCList = "" Or Me.proFITList = "" Then Exit Sub
a = Left(Me.PCList, 3)
b = Left(Me.proFITList, 3)
C = Year(Date)
If Month(Date) > 9 Then C = C + 1
myName = UCase(a & b & C)
lr = ws.Cells(Rows.Count, 3).End(xlUp).Row
lastnum = 1
For x = 1 To lr
If Left(ws.Cells(x, 3), 13) = myName Then
'find last number that applies
lastnum = Right(ws.Cells(x, 3), 3) + 1
End If
Next x
lastnum = Format(lastnum, "00#")
Me.EPCNum = myName & lastnum
End Sub

[SamT]

For unsorted names, sets Lastnum to "001" if new MyName

LastNum = 0
For x = 1 To lr
If Left(ws.Cells(x, 3), 13) = myName Then
'find biggest number that applies
lastnum = WorksheetFunction.Max((LastNum, Right(ws.Cells(x, 3), 3))
End If
Next x
'If MyName not found, LastNum = 0

lastnum = Format(CInt(lastnum) + 1, "00#") 'If LastNum was "999", LastNum = "000"
Me.EPCNum = myName & lastnum

[p45cal]

I've just had a thought: shouldn't
If Left(ws.Cells(x, 3), 13) = myName Then
be
If Left(ws.Cells(x, 3), 10) = myName Then
?

[DT909]

Hello
p45cal, thanks your solution seems working.

SamT, I didn't try yours but thanks anyway.

For the moment it seems OK.

I didn't finish to complete the userform so I will probably come back for other issues.

Thanks guys :) you're really great !!!

[DT909]

OK guys, next question :)

When I fill my userform and click on create button, it put all the information in the selected cells. This works quite well.
I would like to add also a button, dynamically, in the last cell of each row.
Then the user can add a hyperlink to this button, to open another excel file. Each link will be different, so this has to be done manually. But I just want to add a button in 1 cell of each row. The caption could just be "Link" and when the user right click on it, he must be able to add the hyperlink.

I hope that my question is clear :)

Thanks in advance for your help

For info : the column number is 12

[SamT]

OK, DT909, Start a new thread. Give a link to this thread.

[p45cal]

OK, DT909, Start a new thread. Give a link to this thread.

…and when you do, include a file which has your setup - no-one's going to try and duplicate it, probably guessing wrongly, resulting in your getting duff advice.