Long array formula into VBA

[gokica]

Thanks to @TMS and his extensive knowledge the following formula was offered to me as a wonderful solution:

Please Login or Register  to view this content.

But because I need a dynamic header in the resulting spreadsheet I must use VBA.

Since it has to be an array, there is a limit of 255 characters. But because of my lack of knowledge with any of this I am unable to divide correctly:

Please Login or Register  to view this content.

I would like to apply the following workaround but do not know where the mistake is:

Please Login or Register  to view this content.

Maybe the code does not work with RC1 formula. I do not know. I am using Excel 2010.

In the end I would like to apply this formula to column B as long as there is content in Column A.

Thank you so much for your help.

[TMS]

But because I need a dynamic header in the resulting spreadsheet I must use VBA.

What does this mean?

Have you seen my recent update to your previous thread?


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

Please ensure that the sample closely reflects the live file.

[gokica]

Thank you so much. Please accept my apologies. The project that I am trying to complete is more complex with multiple tasks.

One of the hardest things for me to do was adding the interpunct with which you have helped extensively.

Although initially I was planning VBA, your perfect formula paved the way to what I thought will be a perfect solution and to instead use formulas.

Attached are two files. The original file is how the software is outputting the data. I need a VBA that will arrange it as per the amended sample attached.

You will notice that the original spreadsheet has a title while in the resulting file this is supposed to be a header. I do not think that this is possible with a formula.

Besides this I also need to sort column A so that cells starting with a character will go to a separate column while cells starting with indent before the text will go separately.

The content will be the same in both spreadsheets although in the samples attached they are different. My mistake.

Thank you so much.

[gokica]

Just an update. I made it work though formulas rather than VBA. In the end I will just manually add the header. All looks great thanks to the interpunct formula which I could never figure out on my own. Thank you so much TMS.

[TMS]

Best to leave VBA alone unless you really need it. Unless you understand it, it is an unnecessary risk.

[gokica]

Agreed! Thank you so much once again. You are a miracle worker.

[TMS]

You're welcome.