EXCEL 批量插入指标批注

=IF((ROW() <10),"T01060000",IF((ROW() <100),"T0106000","T010600"))
=IF((COLUMN() <10),"0000",IF((COLUMN() <100),"000","00"))

1.把批注放入单元格中 

="[指标]
代码:"&IF((ROW() <10),"T01060000",IF((ROW() <100),"T0106000","T010600"))&""&ROW()&""&IF((COLUMN() <10),"0000",IF((COLUMN() <100),"000","00"))&""&COLUMN()&"
币种:1560001
属性:01
日期:
频度:
机构:
扩展1:
扩展2:
期数:1
可编辑:1
颜色:"

T0106  00050 00001

 

2.把单元格中的批量，改为批注

 

Sub 指标()
Dim tmpStr As String, m As Range
For Each m In Selection
tmpStr = m.Value
If tmpStr <> "" Then
'添加修改批注内容
If m.Comment Is Nothing Then
m.AddComment tmpStr
Else
m.Comment.Text Text:=tmpStr
End If
'对批注格式进行调整
m.Comment.Visible = True
m.Comment.Shape.Select True
With Selection
.Font.Name = "宋体"
.Font.Size = 9
.Width = 121 '这个数字大小还请LZ自行调整了
.Height = 150
End With
m.Comment.Visible = False
Else
'删除无内容单元格的批注。不需要清除的话，请删除下面一行代码
' If Not m.Comment Is Nothing Then m.Comment.Delete
End If
Next m
'清除选中区域所有单元格内容。不需要清除的话，请删除下面一行代码
Selection.ClearContents
End Sub

提取单元格指标信息
Sub 提取()
For Each ran In Range("D5:D" & Cells(Rows.Count, 3).End(xlUp).Row)
If Not ran.Comment Is Nothing Then
ran.Offset(, 1) = Replace(Mid(ran.Comment.Text, 4, Len(ran.Comment.Text)), Chr(10), " ")
End If
Next
End Sub