0

I am pretty new to VBA - but I am trying to write a function to copy a cell and paste the comments to another cell. I have done quite a bit of searching and haven't found and answer and am too new to VBA to understand how to modify what I did find. Background is I have a report that is pulled from another company that puts images into the comments, but I want to take that report and put it in an easier to read format, without manually copying and pasting things all the time. I tried a pivot table but that doesn't return the values, so now trying to write a VBA function to use with an index match to populate my tracker with the data.

I found a Macro that does exactly what I want, listed below. However I want to use it as function so I can use if statements or an index match. 

Sub CommentCopy()
Range("Q7").Copy
Range("Q8").PasteSpecial xlPasteComments
Application.CutCopyMode = False
End Sub

I also found a function that works that copies the text of comments and pastes it to the adjacent cell which is ot what I want

Function commentof(r As Range) As String
Application.Volatile
If r.Comment Is Nothing Then
    commentof = ""
Else
    commentof = r.Comment.Text
End If
End Function

So I tried to combine the two into this but it is not compiling:

Function commentpaste(r As Range) As String
r.Copy
comment paste = PasteSpecial xlPasteComments
Application.CutCopyMode = False
End Function

I also found this link where it talks about copying to a clipboard in a function not being possible? So am I actually not able to do this? Need guidance with a VBA function to paste values in Excel

Thank you to everyone in advance for your help! Brendan

Brendan Leach
  • 1
  • 4
  • Have you tried https://www.mrexcel.com/forum/excel-questions/621441-how-copy-cell-comments-another-cell-via-vba.html – Ole EH Dufour Mar 15 '18 at 17:17
  • I did see that post but I couldn't figure out how it would help me write it as a function – Brendan Leach Mar 15 '18 at 17:22
  • Can you show an example of what is going in and what is expected out? Are you simply trying to gather all comments in particular sheet and write them into separate rows in another sheet? – QHarr Mar 15 '18 at 17:23
  • Do you have a way I can send you an email? Essentially I have a report that I get sent that has 100's of lines of data - the first column has a hyperlink to the image and in the comment box for that row is the image itself. There are other columns with market, brand name, size, price etc. The report itself is helpful but I want to display it in a timeline by market and brand to see what ads/prices were ran when. So if I can write this function, I can set a pivot table and then use the function to pull the comment with image into the right cell. Does that make sense? – Brendan Leach Mar 15 '18 at 17:27
  • Just found this link too - which makes me think what I am trying to do is not as easy as it sounds? I am not sure I follow how that works for pasting the formats, but if I can get that to work then it should work the same way for pasting the comments correct? https://stackoverflow.com/questions/33921752/copy-pastspecial-cell-format-in-vba-user-defined-function?rq=1 – Brendan Leach Mar 15 '18 at 18:35
  • VBA UDF functions used in Excel formula can only return a value and can't change anything else in the cell or other cells. In short, not possible in Excel formula. – Slai Mar 15 '18 at 20:34
  • As @Slai mentioned, unless the cell you want to paste the comment to is included in the function argument, you cannot change any other cell. – PatricK Mar 15 '18 at 21:59

0 Answers0