Userform textbox -used next smallest unused number from column in textbox.

[dellehurley]

Hi
This is a little hard to explain but here goes.
I have a large database which contains details about associated files. The database generates the new file name and part of that file name is a number. Currently there are a lot of missing numbers and gaps. I want excel to use VBA to find the smallest number in the sequence that has not already been used and place that in a textbox on a userform.
I have found this Youtube video and it is sort of doing what I want too, except I need the answer to populate a text box on a userform and be dynamic. This is a link to it Mr Excel & excelisfun Trick 103: Find Missing Numbers In A Series

File Name	File No.	File Type	Event	Ext	RIN	Full Name	Date of Event	Description
FBi0243.pdf	0243	File	Birth	pdf	42568	Doe, Jane	01/05/2019	Birth Transcript
PMa0452.jpg	0452	Picture	Marriage	jpg	4857	Smith, John	02/03/2018	Wedding
PMa0452.jpg	0452	Picture	Marriage	jpg	5638	Jones, Ann	02/03/2018	Wedding

This is an example of the database. The File No. column is the number I want to generate. The same file name maybe duplicated several times if the file relates to several people eg PMa0452.jpg in table above. But the number should only be used once otherwise. The file number is always 4 digits.
Many Thanks for your help.
Dannielle

 

[sykes]

What code have you written already?

 

[dellehurley]

Mr Excel Link
This is a link to the file.
Sorry I don't have any code at the moment I use a dynamic combo box but it does not work very well. I have to update it manually all the time.

 

[mikerickson]

If FileNo. is in column B, you could use code like

Dim i as Long

With Range("B:B")
    For i = Application.Min(.Cells) To Application.Max(.Cells)
        If IsError(Application.Match(i, .Cells, 0) Then Exit For
    Next i

    TextBox1.Text = Format(i, "0000")
End With

 

[dellehurley]

If FileNo. is in column B, you could use code like

Dim i as Long

With Range("B:B")
    For i = Application.Min(.Cells) To Application.Max(.Cells)
        If IsError(Application.Match(i, .Cells, 0) Then Exit For
    Next i

    TextBox1.Text = Format(i, "0000")
End With

Thanks for the quick reply. I get a syntax error on the line

If IsError(Application.Match(i, .Cells, 0) Then Exit For

I quite new to VBA so it is not immediately obvious to me what is wrong.
Dannielle

 

[mikerickson]

Its missing a right parenthesis.

 

[dellehurley]

I

Its missing a right parenthesis.

thought it was going to be obvious . Your a star works perfectly. Thanks