Geting highest number value from list with values which are text/number combinations

Hi all,

I need to write code to generate "Request #" based on selected "Request type" value in the UserForm. Below is a workbook example.

Generate unique number.xlsb

The code should be in the following format: AA_Cus_000000

AA_ - Request type code. From the request type it takes only the first 2 letters
Cus_ - Reference that this is customer. This value is constant for all Request #
000000 - Numeric code for the request type.

Currently my code counts the number of request types in column A (based on the input in the user form).
Based on that it finds the next free consecutive number for the "Request #"

The problem is, when a line which contains "Request #" is deleted, the calculation will not work properly. The total number of "Request type" values in column A for a chosen value will be smaller then the next consecutive number.

In the list in the attached file are removed 3 rows. The next "Request #" for Request type AAG3 should be "AA_Cus_000015". But my calculation will give "AA_Cus_000012" (since there are already 11 values for request AAG3 in column A).

Can anyone advice how can I get the highest number value (last 6 characters) in column B, based on the selection for the "Request type".

Thank you in advance.

Igor

Hi Sourabh,

Thank you for your code.

I've change

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and it works.

The only problem is that it will work only if there are already some values in row 7. If there are no values in row 7 does not generated code in the user form.
Also, if there are some empty cells in column A, the code can overwrite the last already existing values. I need to find the last filled cell in row A, and insert the data below it.

Thank you in advance for your help.

Igor

Hi Sourabh,
IT WORKS PERFECTLY!!!!
Thank you for your help!!!
Cheers.
Igor

Hi Sourabh,
IT WORKS PERFECTLY!!!!
Thank you for your help!!!
Cheers.
Igor