Solved

Excel: Extract Comments With Name to Separate Worksheet with VBA

Posted on 2014-01-30
2
426 Views
Last Modified: 2014-01-31
All:

I have been a member for several years and love Experts Exchange.
I usually find what I need by searching and have rarely had to ask a question.
At this point, however, I haven't found what I need and don't exactly know how to go about it.  

Basically I have a row of data.  In that row of data I have information contained in the cells and several cells with comments as well.

I would like to extract the name on the row and the comments on that same row to another worksheet.  The name and the comments should be in the correct order in separate cells in the same row on the new worksheet.

I'm looking for something like:
Name           Comment 1           Comment 2           Comment 3

I have uploaded a sample spreadsheet to show the original data layout (Input) and the desired output (Output).

I am not new to programming, however, please explain any proposed solution in programming comments if you don't mind.  I find I always learn something that way.

Thanks in advance for any assistance you may be able to provide.
CommentExtractExample.xlsm
0
Comment
Question by:jbcarrico
2 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
Hi,

pls try

Sub macro4()
Sheets("Input").Activate
For Each c In Sheets("Input").Range(Range("A5"), Range("A" & Cells.Rows.Count).End(xlUp))
    Sheets("Output").Range("A" & c.Row - 3) = c
    Sheets("Output").Range("B" & c.Row - 3) = c.Offset(0, 4).Comment.Text
    Sheets("Output").Range("C" & c.Row - 3) = c.Offset(0, 5).Comment.Text
    Sheets("Output").Range("D" & c.Row - 3) = c.Offset(0, 6).Comment.Text
    Sheets("Output").Range("E" & c.Row - 3) = c.Offset(0, 7).Comment.Text
Next
End Sub

Open in new window

Regards
0
 

Author Closing Comment

by:jbcarrico
Comment Utility
Rgonzo1971:

This is exactly what I needed.
Thanks so much for your quick response.
The only thing I needed to add was "On Error Resume Next" because some of the fields in my Comments matrix didn't have comments.

Thanks so much.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

My experience with Windows 10 over a one year period and suggestions for smooth operation
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now