Like and see, form a habit; Martyrs old age, ambition.

Wechat search [Yixin Excel] to pay attention to this different we-media person.

In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.

A few days ago, a fan asked: is there a way to batch edit the contents of cells into annotations, and the contents of each cell are different, mainly the invoice code?

We know that batch adding the same comments to the cell area can be done by using the comment option in the selective paste, while still copying the existing comments.

The fan’s question, therefore, cannot be solved directly with existing features, but the power of Excel does leave a way back for solving complex problems: VBA. Today, I will share how to batch convert the contents of different cells into comments.

The Sub subroutine in VBA can be executed by macro. The other subsubroutine is not explained in detail here, but will be discussed in the VBA series.

1. The contents of the cell itself become annotations

Let’s start by simply changing the contents of the cell itself into comments for the cell.

As shown above, add the contents of the C4:C9 cell area as your own annotations.

Operation is also very simple, first click “development tools” TAB “code” command group under the “Visual Basic”, that will pop up the VBA development form, then in the “VBAProject” → “Microsoft Excel object” below select the current worksheet, and right click the mouse to choose “insert” under the “module”.

If you do not have the “Development Tools” TAB, you can use the “Options” under the “File” TAB to bring up the “Excel Options” dialog box, click “Custom Functional area”, and select “Development Tools” on the main TAB.

We renamed the default “module 1” to the “Annotation Sub subroutine” and added a blank editor to the gray area on the right.

Sub add cell content to note itself () Dim r As Range, rs As Range Set rs = Selection For Each r In rs If r.Value <> "" Then If Not r.Comment Is Nothing Then r.Comment.Text Text:=r.Text Else r.AddComment r.Text End If End If Next r End SubCopy the code

And copy the above code into the blank editor, and then click Save, the pop-up dialog box click Yes, and then close the VBA form.

Then select C4:C9 cell area, click “development tools” TAB “Code” command group under the “macro”, then select the above code method name “add cell content as its own annotation”, click Execute.

Let’s take a look at the full animation:

2. Change the contents of the previous column of cells to the next column of comments

The implementation is simpler, but the actual application scenario is less. Maybe we want to turn one column of content into another column of comments.

In the figure above, we want to convert the contents of column B into comments for column C.

Given the previous example, this is easy, and we only need to make a few code changes. The modified code is as follows:

Dim r As Range, rs As Range Set rs = Selection For Each r In rs If r.Value <> "" Then If Not r.Comment Is Nothing Then r.Comment.Text Text:=r.Offset(0, -1).Value Else r.AddComment r.Offset(0, -1).Value End If End If Next r End SubCopy the code

R.ffset (0, -1).Value indicates the Value of the previous cell in the same row.

The operation process is the same as the above method, here will not describe in detail, directly look at the dynamic diagram:

Of course, there are some flaws in this method, because you can only write the value of the column in the code. Is it possible to use a variable to input the value of the column? Can that be done in the form of a custom function? I’ll leave those two questions to you.

Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.

If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.

This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.