Merge text from multiple cells in row into a comment box in a different cell?

[IHRAcer]

I have a workbook that requires a large quantity of data to be on one sheet for sorting purposes, and as a result there's a lot of side scrolling going on. In an effort to reduce the number of columns in overall width, I would like to move some of the info further to the right, but auto-populate a comment box with the text from four of the cells in each row into one of the first cells for each row. For example:

Column A contains the Customer ID Number. Column T is Customer Name, Column U is Customer Address, Column V is Customer City, and Column W is Customer State. The sheet is auto populated via macro from a master sheet, and is about 700 rows. I would like to have a macro that copies the info from Columns T,U,V, and W and pastes that respective info for each row into a comment box in Column A so that I can hover over it and see it at a glance, instead of scrolling sideways to find it when necessary.

I have been trying to figure this out myself, but I am a novice with macros and am lost. Any help would be appreciated.

 

[IHRAcer]

Good morning everyone! I'm giving this a bump in the hopes that someone who might not have seen it last night does now.

 

[yky]

Try this one. It adds comment for cells A5 to A10, assuming they don't already have a comment. Not tested.

Sub add_comment()

Dim i as Integer

With ThisWorkbook.Sheets("Sheet1")
for i = 5 to 10 step 1

.Cells(i,1).AddComment .Cells(i,20).Text & Chr(10) & .Cells(i, 21).Text & Chr(20) & .Cells(i,22).Text & Chr(10) & .Cell (i,23).Text

Next i

End With

End Sub

 

[Fluff]

Another option

Sub IHRAcer()
   Dim Cl As Range

   With Sheets("Sheet1")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Not Cl.Comment Is Nothing Then Cl.Comment.Delete
         Cl.AddComment .Evaluate("textjoin(char(10),0," & Cl.Offset(, 19).Resize(, 4).Address & ")")
      Next Cl
   End With
End Sub

This will delete any existing comments

 

[IHRAcer]

Try this one. It adds comment for cells A5 to A10, assuming they don't already have a comment. Not tested.

Sub add_comment()

Dim i as Integer

With ThisWorkbook.Sheets("Sheet1")
for i = 5 to 10 step 1

.Cells(i,1).AddComment .Cells(i,20).Text & Chr(10) & .Cells(i, 21).Text & Chr(20) & .Cells(i,22).Text & Chr(10) & .Cell (i,23).Text

Next i

End With

End Sub

Sorry, this one gave me a syntax error, and while I've adopted and modified some VBA in the past to suit my needs, we're delving into a whole new level for me and I don't know enough about it to fix it. I do appreciate you taking the time to try to help me though! Thanks!

 

[IHRAcer]

Another option

Sub IHRAcer()
   Dim Cl As Range

   With Sheets("Sheet1")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Not Cl.Comment Is Nothing Then Cl.Comment.Delete
         Cl.AddComment .Evaluate("textjoin(char(10),0," & Cl.Offset(, 19).Resize(, 4).Address & ")")
      Next Cl
   End With
End Sub

This will delete any existing comments

Fluff, this worked perfectly! Thank you so much for sharing! I do have two questions, if you've got a little more time for me:

1) Will this loop for each row until it runs out of rows, no matter how many?

2) Just so I can better understand, can you explain something for me? I can see from your script that the Cl.Offset(, 19) is what tells it to start at Column T, and I'm assuming that the .Resize(, 4) is what tells it to pull the text from the four columns, but what is the (char(10),0, part for?

If you don't have time to explain I certainly understand, and I appreciate what you provided! Have a great day!

 

[Fluff]

1) Yes, it looks for the last cell in col with a value.
2) You understand correctly and the Char(10) is a linefeed, like using Alt Enter in a cell.

 

[IHRAcer]

1) Yes, it looks for the last cell in col with a value.
2) You understand correctly and the Char(10) is a linefeed, like using Alt Enter in a cell.

Awesome! Thanks again, I really appreciate it!

 

[Fluff]

Glad we could help & thanks for the feedback.