Moving Excel Comments into Cells 3

[LittleSmudge]

I have a big challenge in moving masses of data from Excel comments into cells.

It is expalined in detail on the MSOffice board thread68-398003







G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[VulcanJedi]

1. Please don't double post, its confusing and annoying when trying to navigate the forums and help unattended threads.

2. I wanted to address the issue, that XL comments appear able to store much more vast amounts of data in them, while the capacity a cell is very limited relatively. So while I think its possible to code the extraction of the comments field, if your 'user(s)' put in a LOT of data, you could get truncated information?

Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[RobBroekhuis]

What's wrong with

on error resume next
for j=1 to 40
for i=1 to 400
cells(i,2*j)=cells(i,2*j-1).comment.text
next i
next j

Rob

 

[acron]

Try the following :

Dim CommentRange As Range
Dim oCell As Range
    Set CommentRange = Cells.SpecialCells(xlCellTypeComments)
    For Each oCell In CommentRange
        oCell.Offset(0, 1) = oCell.Comment.Text
    Next

which will insert the comment in the cell to the right (use Offset(0,-1) to use the left cell).

The code above uses the enire sheet, so you could change Cells.SpecialCells to something more restrictive if you want to work within a given range of cells., such as [A1:Z4000], or indeed Selection, which would require that you uselect the range before running th ecode.

A.C.

 

[LittleSmudge]

Nothing wrong at all Rob - that seems just what I'm looking for. - And will save the effort of inserting the additional columns throughout.


Thanks also to Acron. I think there is something in your post I'll gain from too.


Tranpkp,
Thanks for the warning in point 2. I believe the data in the comments is fairly limited - withing the scope of an excel cell anyway but I'll bear that in mind.




As for your first comment - I would ALWAYS prefer to see a question in ONE place and links provided to it on multiple boards to increase readership/coverage rather than the time wasting that inevitably results when people post ( unlinked ) the full question to multiple boards.
I notice that you are a relative novice on the TekTips board ( although I cannot infer your experience on other boards of course ). You will find that unlinked multi posting is a prolific problem on TekTips as there does not seem to be a corporate policy on evicting transgressors.

However, increasing readership is often desirable and the universally accepted approach ( in all the other boards I visit ) is to post links. After all this thread is a case in point. I posted the original question and a link on another board. Only when they generated Zero replies did I try this board and received useful advice and two very helpful solution options.

Once you've spent time answering someone elses query on one board, only to find out later that it was covered completely by someone else the day before on another board - and then have that happen 3 or 4 times in a day - you'll soon get to see that posting links is a FAR more appropriate solution.
Afterall the TekTips site does have that great big navigator line at the top of the page to let you know which subboard you are currently on.







G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[rmikesmith]

FYI,

In the case of XL 2000,

Maximum text length a cell can hold = 32,767 characters although only 1,024 characters are displayed in the cell (formula bar will display all 32,767).


Regards,
Mike