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

IHRAcer

New Member
Joined
Apr 6, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

IHRAcer

New Member
Joined
Apr 6, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 7, 2011
Messages
1,833
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Another option
VBA Code:
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

New Member
Joined
Apr 6, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Apr 6, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Another option
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Apr 6, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top