Copying multiple lines cells into comment - not working for long strings- VBA

[uolip17]

Dear all,

I've been using the website for long time now, always finding the resources I needed to keep on building my sheets.
However I have been struggling with the following for a few hours, and can't find a proper answer, anywhere, so far...

Situation:
I have a whole sheet of numbers, used as a recap from a database. My idea is to sum up what builds up each cell in its comment, through a VBA.

The way I worked on it:

1. I create the exact same sheet layout with no numbers, but comments instead
2. I create a macro to copy all cells content to cell comments
3. I copy the entire sheet comments to the recap sheet

Each cell with the content that will be used as formula is build up by a formula and looks like that:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a1[/TD]
[TD]b1[/TD]
[TD]c1[/TD]
[TD]a1 - b1 - c1
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a2[/TD]
[TD]b2[/TD]
[TD]c2[/TD]
[TD]a2 - b2 - c2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a3[/TD]
[TD]b3[/TD]
[TD]c3[/TD]
[TD]a3 - b3 - c3[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

I then put all of it together with +concatenate(d1,char(10),d2,char(10),c2) to have them nicely arranged.


I then run the macro:

Sub CellToComment()


Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Select range"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.NoteText Text:=Rng.Value
Next


End Sub

I select the range (or for my test, my cell), and it gives the cell content as comment, as planned.

Issue:
When I try it for a real scenario, with long strings, it does not.
It takes 2 lines for long strings maximum.

Any idea...?

I thank you in advance for your time!
Best regards,
uolip17