Need algorithm help

[aMember]

Have unique "keys" in the format of 1-111 in Excel. Want to be able to generate thru VBA the next sequential key.

Can't just add one to 1-111 because 1-111 is a string. Need to be able to get from 1-999 to 2-000 also.
Any suggestions on how to handle this most efficiently?

Andrea

 

[TheBitDoctor]

How about:

=LEFT(A1,1)+NOT(MOD(RIGHT(A1,3)+1,1000)) & "-" & RIGHT("000" & MOD(RIGHT(A1,3)+1,1000),3)

Ron

 

[TonyJollans]

Hi aMember,

To do it in VBA, this should work ..

Dim Key As String
Key = "1-994"

' For example

Key = Format(Replace(Key, "-", "") + 1, "#-000")

To do it in a worksheet, Autofill does everything except the switch from 1-999 to 2-000, so depending on exactly what you are doing you might be able to make use of that.

Enjoy,
Tony

-------------------------------------------------------------------
Wishing all Tek-Tippers a Happy and Prosperous New Year

 

[MichaelRed]

In VB/VBA, the subject has been discussed numerous times, with a number of "professional" responses. See thread222-97999 for at least a few well done procedures along this line.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[aMember]

That really helped. Ron, I tweaked what you gave me and incorporated it into my VBA code. Works like a charm.

Greatly appreciated.
Andrea

 

[dc20]

Hi Tony,

Did you mean Substitute instead of Replace ?
worksheetfunction.substitute

I like the theory of removing the dash and incrementing.

 

[TonyJollans]

Hi dc20,

Replace is new in VBA 6; it does the same as the Worksheet Substitute function. So in Excel 2K (and above) you can use either; in earlier Excel you need to use Substitute; in other Office 2K apps you can use Replace; in other pre-2K Office apps you need to code your own function.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[TonyJollans]

Hi again,

Just tried to use the same method without VBA ..

=INT(((SUBSTITUTE(A1,"-","&quot)+1)/1000)&"-"&RIGHT(SUBSTITUTE(A1,"-","&quot+1,3)

.. but it's a bit more long winded.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.