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:

	a	b	c	d
1	a1	b1	c1	a1 - b1 - c1
2	a2	b2	c2	a2 - b2 - c2
3	a3	b3	c3	a3 - b3 - c3
...	...	...	...	...

<tbody>
</tbody>

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