4 replies
by scox2500 Accepted solution
by tobif Accepted solution
It's easier to copy something that is visible in a cell.
You coul'd add an empty column and then write a macro function to reveal the comment in a cell on the same row.
In Excel 2003, do like this:
Press Alt-F11 to open the VBA window.
Menu - Insert - Module
Paste the following text into the module window
Function ShowComment(ThePointer As Range)
ShowComment = ThePointer.Cells(1, 1).Comment.Text
End Function
Now you can close the VBA window. The module will be a hidden attachment to the Excel file.
In the Excel sheet, you can now use your new function. I.e. In order to show the comment from Cell A1, in whatever position you want, you enter the formula =ShowComment(A1)
Note1: This code is NOT fool proof.
Note2: Security barriers in Excel may block this macro after you've saved and reopened the file. With self-signing certificate for VBA, you may mark this macro in such a way, so that the macro will run even if you block other macros.
by scox2500 Accepted solution
thank you for your response--it was very helpful. I have another problem:
I need to separate each line item inside the Comment note to a different cell. Can u help me with this? Here is an example of the data in the comment:
DRPV held 6/12/10 (new cell)
DRPV.1 held 7/12/10 (new cell)
DRPV.2 to be held 9/1/10 (new cell)
by tobif Accepted solution
Where you write (new cell), do you have any particular character?
I guess you have varying count of rows in different comments, just to crack a neat table design (This typically happens when you try to put in Relational data into a 2-dimensional matrix...)
If you have a specific character to search for, then you can use (I'm referring to Excell 2003, if needed some smart people with mouse-arm will tell us where to find the same things in Office2007)
Menu-Data-"text to columns", indicate "delimited" and enter that special character in the "Other" field. (Unfortunately, you must have one single delimiter character, "DRP" won't do,
UNLESS you do a little trick, select the extracted data, press CTRL+H and replace all occurrences of "DRPV" with "@DRPV".
Now you have your delimiter! :)
You can, of course also write a nice formula for splitting up this, that approach was touched recently in another thread here. "Extract a string from a variable start position" http://bit.ly/9Rvba1
This discussion is closed to new replies.
Trending Software posts
Sort by Trending