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:
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 WorksheetDim LR As LongDim Rng As RangeSet Ws = ActiveSheetLR = Ws.Range("A" & Rows.Count).End(xlUp).RowSet 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 WithNextEnd Sub
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.