Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How top copy cell comments
If you change your routine to use a cell in another column, you could use the
=vlookup() and your life may get better. I think it would be time worth spending. But if you can't... saved from a previous post: Not by anything built into excel--but you could use a User Defined Function. Do you want to try a little macro? Option Explicit Function VlookupComment(myVal As Variant, myTable As Range, _ myColumn As Long, myBoolean As Boolean) As Variant Application.Volatile True Dim res As Variant 'could be an error Dim myLookupCell As Range res = Application.Match(myVal, myTable.Columns(1), myBoolean) If IsError(res) Then VlookupComment = "Not Found" Else Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res) VlookupComment = myLookupCell.Value With Application.Caller If .Comment Is Nothing Then 'do nothing Else .Comment.Delete End If If myLookupCell.Comment Is Nothing Then 'no comment, do nothing Else .AddComment Text:=myLookupCell.Comment.Text End If End With End If End Function This kind of function could be one calculation behind. If the comment in the table changes, then you'll want to force a recalculation before you believe the results. Application.volatile true means that excel will recalculate each of these formulas each time excel recalculates. You may notice a slowdown in your workbook. If you remove this line, then the results in the cell will be ok, but the comment may be wrong. (The results in the cell should always be ok--it's the comment that's the trouble.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Into a test cell and type: =VlookupComment(a1, 'sheet 33'!a:e, 5, false) It looks a lot like =vlookup(). =================== If you decide that you want to use an adjacent cell (I'd do my best to use this!), you could use a different function to retrieve those comments. Also saved from a previous post: You can retrieve the text from a comment with a userdefined function like: Option Explicit Function GetComment(FCell As Range) As Variant Application.Volatile Set FCell = FCell(1) If FCell.Comment Is Nothing Then GetComment = "" Else GetComment = FCell.Comment.Text End If End Function Then you can use it like any other function: =getcomment(a1) But be aware that the function won't evaluate when you just change the comment. It'll be correct when excel recalculates. (Hit F9 to force a recalc.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm JRW wrote: I receive a new file monthly that has some of the same part numbers in the file but is not the same. I am currently using Vlookup to copy the data that I need from the old file (last months) to the new file, however, comments that were added to the cells the previous month aren't copied over using vlookup but just the cell data. I need a means to also copy over the cells comments from the last months file. Any help would be appreciated. -- Dave Peterson |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy comments box | Excel Discussion (Misc queries) | |||
How to copy comments to cell as normal text? | Excel Discussion (Misc queries) | |||
I need to copy only those cell where comments is there | Excel Worksheet Functions | |||
Copy Comments to a cell | Excel Programming | |||
Creating Macro to copy information from cell into another cell using Add Comments | Excel Programming |