VBA Macro which create new lines by codes

[DocAElstein]

@ P45cal
Hello Pascal
I agree with you on that. I think its inevitably going to get worse, because forums are increasingly just seen as an alternative to a Google search and/ or an answer and question section working on Artificial Intellisense. The idea of Human things like politeness and feedback is going out of fashion.

I think the forum is for extending the knowledge and encouraging discussions in Excel stuff. So regardless of OP reaction, adding extra alternative solutions like you did is always worthwhile for the greater long term good. ( In 10 years, when I finally figure out what Power Query is about , Lol, then it will be interesting to come back and see how you did here something in Power Query compared to like I did it here with VBA. )
When I finally take over this place, my first priority will be to trim out the crap and, very importantly, my main priority will be to keep the place here as long as possible. So feel free to keep adding replies. They won’t be lost!

( Just a couple of very minor points in this case though:
_ I might be responsible for the .xls files. He started with .xlsx, but I like to do everything in .xls first, so I probably first introduced using .xls and so I am responsible for any confusion on that one..
_ I think Email notifications of replies in Threads you are subscribed to may be broken at excelfox just now, so OPs might miss replies. I don’t know if it’s a temporary glitch or may need some work to fix. ( Let me know please if Email notifications are working for you, assuming you have your settings to get them.) All my setting are to get all Email notifications. But since a month they have stopped working for me.
( Because excelfox has such little traffic, my temporary solution currently is to send an extra Email to OPs to tell them about any reply, as I did for the OP in this Thread… - I have access to OPs personal EMail and I abuse that privilege at will
Later I intend giving that sort of "power" to all helpers , such as yourself. Those that help should have all the tools available, IMO )… )

---

---

@ Alex
Hi Alex,

...I've faced a problem: in my original list there are some codes that start with 0, for example, '060-062'. I decided to use if-clause and apparently it works, but the current problem lies in 2c part, in particular, in arrOutTempCT() that ignores nulls at the beginning of a code since it is variant and makes them just '60; 61; 62'. I've got no idea how to make it put codes without eradicating 0. I tried to change arrOutTempCT() from variant to string, but certainly it didn't work
...... i think there can be something done with arrOutTempCT()

It did sound reasonable to me initially that the transposing ( that is what arrOutTempCT() is all about ) might be the problem.
However I took a quick look here: , https://excelfox.com/forum/showthrea...ll=1#post15541 , and that transposing does not seem to be the problem. If you look at the Watch Window results, you can see we maintain our string in the array arrOutTempCT()

My guess at this stage is that we have two issues :
_(i) Code section ' 2b is using maths to modify something like 3-5 type data into 3 4 5. So this is likely always to convert 03-05 into 3 4 5
You appear to have had some success in curing that with this bit.

            Dim FrstSym As String
             Let FrstSym = Left(NRng, 1)
                If FrstSym = 0 Then
                Let NRngMod = NRngMod & "0" & Cnt & "; "
                Else
                Let NRngMod = NRngMod & Cnt & "; "
                End If
            Next Cnt

If you then look once again at your array contents, then you still have what you want : For example in your test data for row with 18; 061-069, this here: https://excelfox.com/forum/showthrea...ll=1#post15542 , is what you see.
Once again, the transpose is not the problem

It looks initially as if you have already cured the main problem yourself

_(ii) The remaining issue I think is the whole “can of worms” area of Excel deciding it knows better than you what you should see.
What Excel is doing is deciding that you are pasting in a number when you paste in like 061. It decides that the number is 61
There are a lot of different ways to overcome such problems.
The simplest way is to manually change the format of column C in the New worksheet to text, as I have done in the uploaded returned file. Basically, as I understand it, using text format short circuits Excel trying to modify anything – basically with text format you get what you give.
If you want a different solution, then we can think again later…

In short, your modified macro is giving what you want. But Excel is changing the format you give it. The simple cure I have done is manual. We can come up with various ways to overcome the problem in the coding, should you prefer to start with a virgin New worksheet with all default formats.

Alan