Adding Numbers As Strings And Keeping Format

I have a sheet which has a series alphanumeric references, the reference is always BISxxxx where xxxx is a four digit number starting at 0001 and progressing 0002, 0003 etc.

I have a button on my sheet which runs VBA code to add a new row with the new reference in the progression.

The part of my code that calculates the numeric part of the new reference is

Please Login or Register  to view this content.

In this instance I get the following

OldRef = BIS0001
OldRefNumber = 0001
NewRefNumber = 2

My problem is that I am losing the format of 000x, how to I make sure this is kept?

When I reach the tenth reference is needs to be 00xx and so on.

Hope this makes sense and someone can help me.

Ed

Maybe this:

Please Login or Register  to view this content.

Akuini,

A perfect solution. Thanks.

Ed