Copy cell contents for multiple columns and put into a comment for a cell in another column

I have a daily routine that involves downloading a list that I need the cell contents of several columns to be populated into a comment for the cell in column A for the same row -- but also have the heading (row 1) for each column to preface the data populated in the column.  

For the attached I'd like the following column values populated into the comments, in this order:
CUSIP (Col I)
Sec Short Name (Col L)
Trade Date (Col J)
Broker ID (Col K)
Trader Login (Col M)
Security Type (Col N)

So for A3, I'd like the comment to be populated with the following:
CUSIP: 36251MAA9
Sec Short Name: GMALT 2016-3 A1
Trade Date: 3/23/2017
Broker ID: JOURNAL
Trader Login: KBBUCKLAND
Security Type: ABS Auto

Repeat all the way down the list.

I'd also like it to have the ability to not breakdown if a cell value is empty.  

Thank you

Please note this is an expanded request to the thread answered below:

https://www.experts-exchange.com/questions/29011301/Copy-cell-contents-for-two-columns-and-put-into-a-comment-for-a-cell-in-another-column.html?anchor=a42062290¬ificationFollowed=186303770#a42062290
TBLTall-Worksheet1-expanded.xls
Kevin BucklandInvestment OfficerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
Hi Kevin,

Please try below:
Sub AddComments()
Dim Ws As Worksheet
Dim LR As Long
Dim Rng As Range
Set Ws = ActiveSheet
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Ws.Range("A3:A" & LR)
For Each cell In Rng.Cells
    Dim I As Range
    Dim L As Range
    Dim J As Range
    Dim K As Range
    Dim M As Range
    Dim N As Range
    Set I = cell.Offset(0, 8)
    Set L = cell.Offset(0, 12)
    Set J = cell.Offset(0, 10)
    Set K = cell.Offset(0, 11)
    Set M = cell.Offset(0, 13)
    Set N = cell.Offset(0, 14)
    With cell
            .ClearComments
            .AddComment "CUSIP: " & I.Value & " Sec Short Name: " & L.Value & " Trade Date: " & J.Value & " Broker ID: " & K.Value & " Trader Login: " & M.Value & " Security Type: " & N.Value
    End With
Next
End Sub

Open in new window

0
ShumsDistinguished Expert - 2017Commented:
Please find attached for your reference:
TBLTall-Worksheet1_v2.xlsm
0
Kevin BucklandInvestment OfficerAuthor Commented:
This worked good all in all but a couple values were populating from a wrong column offset.  

I'd ideally like each one to start on a new line in the comment.  Is there a way to put a hard return after each value ? For instance the "Sec" (beginning of "Sec Short Name") displays after the cusip populated. I'd ideally like each one to start on a new line just for readability.  

This is the change I made for the column offset:
Sub TBLTcomments()
'
' TBLTcomments Macro
' Takes various info including broker and CUSIP and puts into cell comment of the security description.
' Requires SHEET1 EXPANDED COLUMNS instead of the old SHEET 1 as the starting point.
'
'
Dim Ws As Worksheet
Dim LR As Long
Dim Rng As Range
Set Ws = ActiveSheet
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Ws.Range("A3:A" & LR)
For Each Cell In Rng.Cells
    Dim I As Range
    Dim L As Range
    Dim J As Range
    Dim K As Range
    Dim M As Range
    Dim N As Range
    Set I = Cell.Offset(0, 8)
    Set L = Cell.Offset(0, 11)
    Set J = Cell.Offset(0, 9)
    Set K = Cell.Offset(0, 10)
    Set M = Cell.Offset(0, 12)
    Set N = Cell.Offset(0, 13)
    With Cell
            .ClearComments
            .AddComment "CUSIP: " & I.Value & " Sec Short Name: " & L.Value & " Trade Date: " & J.Value & " Broker ID: " & K.Value & " Trader Login: " & M.Value & " Security Type: " & N.Value
    End With
Next
End Sub

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kevin BucklandInvestment OfficerAuthor Commented:
Also is there any way to expand the size of the comment so when I just hover over it I can see it all? Currently I have to expand the size of each comment to be able to see all the data in the comment.
0
ShumsDistinguished Expert - 2017Commented:
Yes Kevin, I did noticed, give me few mins, I will change it.
0
ShumsDistinguished Expert - 2017Commented:
Kevin,

Please find attached...
TBLTall-Worksheet1_v3.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin BucklandInvestment OfficerAuthor Commented:
Works like a charm!  Thanks!
0
ShumsDistinguished Expert - 2017Commented:
You're Welcome Kevin! Glad I was able to help again :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.