VBA Macro which create new lines by codes

[DocAElstein]

Another Macro variation , - in a series of about a thousand different ways to do the same thing in VBA….

I have done a minor modification to my original macro offering here: https://excelfox.com/forum/showthrea...ll=1#post15550
It has a modification to overcome the problem of there are some codes that start with 0, for example, '060-062'.
I have replaced the solution of Alex’s “….'If...Then...Else Statement' so that the macro would add '0' at the beginning of some codes if there was one initially……”
Instead of using that idea of Alex, I am using the idea that P45cal has used to …. …..the leading zeroes (even if there are more than one) being guided by the length of the string before the -…..

I had thoughts of doing something like that myself originally, but I was not quite sure how to do it.
( In particular I was not quite sure how this thing works..
Format(__ , ___ )
I also did not know we have a Application.Rept( __ , ___) thing available
( ' Rept: https://docs.microsoft.com/de-de/off...tfunction.rept ) )
So I have learnt about those things thanks to P45cal's extra solution.

Just for further reference, Here is what’s going on there: ( P45cal's way of doing it, which I have copied into my latest version, Sub AlexAlanPascal() )
We use the first number in a set like 061-069 , so in that example 061, to determine the “length” of digits, which we put in variable Padding.
It will be 3 in that case:
Padding = Len(StrtN)
Padding = Len("061") = 3 ' "061" is character Length of three
Then, when we add the numbers to the string in the loop , we do it slightly differently to how I originally did it.
Instead of this line
= NRngMod & Cnt & "; "
For the example considered, we will have had:-
= NRngMod & 61 & "; "

I have now this modified line
NRngMod & Format(Cnt, Application.Rept(0, Padding)) & "; "
For the same example here we will have:-
NRngMod & Format(61, Application.Rept(0, 3)) & "; " ' Rept: https://docs.microsoft.com/de-de/off...tfunction.rept
=NRngMod & Format(61, 000) & "; "
=NRngMod & "061" & "; "
( actually, the last bit might be NRngMod & 061 & "; "
I am not quite sure exactly what is returned by Format(61, 000) , but that's less important to the overal idea )
_._________________

One small extra point of interest , which demonstrates how VBA is often kind to us and takes what looks like a number to be a number.. In some ways it shows that the disadvantage we experienced in Excel taking a text as number, can , actually, in other situations be a useful feature.
In my experience, at least in VBA, the advantages of this feature outweigh the disadvantages. But its not a clear cut thing. For me it’s a ratio of about 20%:80% in terms of Disadvantage:Advantage.
Other people may come to other conclusions, depending on their actual applications.
I use and rely on this feature a lot, because I like to use string manipulation and often like to keep numbers held as strings. For example using strings for numbers often avoids Excel’s annoying unpredictable changing of a number's format. In general, a string is taken by VBA as a string: What you give it = what you see = what you get. But, the useful thing I find is that I can use those strings in formulas and functions in VBA , and VBA will usually take them as the number they look like. We sometimes say VBA will “coerce it into a number if it can”

Example : In my coding, in the above example, I loop from
StrtN To StpN
Which comes out in the example as
"061" To "069"
In some programming languages that would crap out and error, due to something like a Type mismatch because we are putting texts where numbers are expected. But in VBA it conveniently decides its doing this
61 To 69
There will be no error, and I can continue to use those string variables in string manipulations and/ or most VBA mathamatical formulas and functions.
Using P45cal's way of doing it , for example, I am able to get the correct length of characters in order to maintain the format.
Padding = Len(StrtN)
Padding = Len("061") = 3 ' "061" is character Length of three
If I had been using number variables for things like StrtN , then that idea likely would not work in my macro, since VBA may have changd the format, removing any leading zeros...


Alan