Option Explicit
Sub tt()
Dim flag As Boolean
Dim i As Long, x As Long, y As Long, z As Long
Dim sep_ As Long, start_ As Long, end_ As Long
Dim str_ As String
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row + 1
If flag = False Then
If Cells(i, 1).Value <> "" Then flag = True: x = i
ElseIf flag Then
If IsNumeric(Mid(Cells(i, 1).Value, 2, 1)) Then 'вот это можно делать один раз по группе, но лениво искать решение
sep_ = InStr(Cells(i, 1).Value, ":")
start_ = InStr(Cells(i, 1).Value, "[") + 1
end_ = InStr(Cells(i, 1).Value, "]") - 1
End If
If Cells(i, 1).Value = "" Then
flag = False: y = i - 1 ': Debug.Print "x= " & x & " y= " & y & " start= " & start_ & " end= " & end_ & " sep= " & sep_
With Sheets(2).Cells(Int(Mid(Cells(i - 1, 1).Value, start_, sep_ - start_)), Int(Mid(Cells(i - 1, 1).Value, sep_ + 1, end_ - sep_)))
For z = x To y
str_ = str_ & Cells(z, 1).Value & Chr(10)
Next
str_ = Left(str_, Len(str_) - 1)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=str_
.Comment.Shape.TextFrame.AutoSize = True
str_ = ""
End With
End If
End If
Next
End Sub
|
