I've got a macro that takes text from cells and puts them into a comment box.

People have now decided that they want the text on a new sheet as well as in
a comment box.

To save me having to reload 180 reports is there any way to take the text
from the comment box and put into cells. I need a new cell (on a new line)
for each line in the comment box.

Each comment box holds a maximum of 5 comments, each comment starts on a new
line in the box. There can be less though.

I can put the code to get it into the comment box if it'll help.

Thanks to everyone that's helped me for the past few requests, managers are
starting to ask for some stupid things now.

N1KO

to adapt ...

Sub test()
Dim rng As Excel.Range
Dim rngr As Excel.Range
Dim rngT As Excel.Range
Dim s As String, v, t, r As Long, c As Long

On Error Resume Next
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts)
If Err Then
Exit Sub
End If
On Error GoTo 0

Set rngr = Nuovo_Range(ThisWorkbook)

For Each rngT In rng
c = 0
s = rngT.Comment.Text
v = Split(s, vbLf)

rngr.Offset(r, c) = rngT.Address(, , , True)
c = c + 1

For Each t In v
rngr.Offset(r, c) = t
c = c + 1
Next

r = r + 1
Next

End Sub
Function Nuovo_Range( _
Wb As Excel.Workbook, _
Optional Nome_base As _
String = "Res") As Excel.Range

Dim b
Set Nuovo_Range = Wb.Worksheets.Add.Range("A1")

Application.ScreenUpdating = False
On Error Resume Next
Do
Err.Clear
b = b + 1
Nuovo_Range.Parent.Name = Nome_base & b
Loop While Err
Application.ScreenUpdating = True

End Function

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"N1KO" wrote:

I've got a macro that takes text from cells and puts them into a comment box.

People have now decided that they want the text on a new sheet as well as in
a comment box.

To save me having to reload 180 reports is there any way to take the text
from the comment box and put into cells. I need a new cell (on a new line)
for each line in the comment box.

Each comment box holds a maximum of 5 comments, each comment starts on a new
line in the box. There can be less though.

I can put the code to get it into the comment box if it'll help.

Thanks to everyone that's helped me for the past few requests, managers are
starting to ask for some stupid things now.

N1KO

You've saved me a lot of work, I've adapted it slightly to change the columns
to rows but other than that its working brilliantly.

Thanks

"r" wrote:

to adapt ...

Sub test()
Dim rng As Excel.Range
Dim rngr As Excel.Range
Dim rngT As Excel.Range
Dim s As String, v, t, r As Long, c As Long

On Error Resume Next
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts)
If Err Then
Exit Sub
End If
On Error GoTo 0

Set rngr = Nuovo_Range(ThisWorkbook)

For Each rngT In rng
c = 0
s = rngT.Comment.Text
v = Split(s, vbLf)

rngr.Offset(r, c) = rngT.Address(, , , True)
c = c + 1

For Each t In v
rngr.Offset(r, c) = t
c = c + 1
Next

r = r + 1
Next

End Sub
Function Nuovo_Range( _
Wb As Excel.Workbook, _
Optional Nome_base As _
String = "Res") As Excel.Range

Dim b
Set Nuovo_Range = Wb.Worksheets.Add.Range("A1")

Application.ScreenUpdating = False
On Error Resume Next
Do
Err.Clear
b = b + 1
Nuovo_Range.Parent.Name = Nome_base & b
Loop While Err
Application.ScreenUpdating = True

End Function

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"N1KO" wrote:

I've got a macro that takes text from cells and puts them into a comment box.

People have now decided that they want the text on a new sheet as well as in
a comment box.

To save me having to reload 180 reports is there any way to take the text
from the comment box and put into cells. I need a new cell (on a new line)
for each line in the comment box.

Each comment box holds a maximum of 5 comments, each comment starts on a new
line in the box. There can be less though.

I can put the code to get it into the comment box if it'll help.

Thanks to everyone that's helped me for the past few requests, managers are
starting to ask for some stupid things now.

N1KO