Good Afternoon Folks,

I have been working on creating a userform that is able to generate me a 'contract number' at the click of a button, this number needs to search through previous entries to not duplicate but also needs to be given a tag dependant on the Selected 'Stream'

for instance the user would fill in the form and would have selected a 'stream' from the user form, after this the generate button would be eligible for clicking to generate the next number that is not duplicated but also given it a tag example:

if the form had been filled in and the stream selected was EWD the button would have to generate the next number 103 in this case and also add a D before the figure as this is the tag i would like to use for the 'EWD' criteria
likewise for the 'SW' Stream it would need to generate the next figure +1 from the last highest figure 6806 in this instance then add a SW before the number.

is this possible to add to my vba macro or am i over complicating the procedure?

after having a good browse around i cannot find much information in regards to this, can anyone help at all?

Thank you in advance.


thedata.xlsm