Ago
03-25-2008, 07:43 AM
how can i merge these two loops in to one?
For k = i To i + j - 1
For l = i To i + j - 1
If k <> l Then
Range("G" & k).comment.Text Text:=Range("G" & k).comment.Text & _
Chr(10) & Range("E" & l).Value & _
" " & Range("L" & l).Value
End If
Next l
Next k
i = i + j - 1
Set myRng = Range("G6", "G" & n1) 'sets the range in myRng
Application.StatusBar = "Adjusting the size of the comments"
'below it sets the size on each comment to autosize
For Each mycell In myRng.Cells
If Not (mycell.comment Is Nothing) Then
With mycell.comment
.Shape.TextFrame.AutoSize = True
End With
End If
Next mycell
in the upper loop i tried: Range("G" & k).Comment.Shape.TextFrame.AutoSize = True
but it doesnt work.
it feels so stupid to have a code that first creates the comment then leaves it, and a few seconds later it has to find the comment again and adjust the size of the comment.
For k = i To i + j - 1
For l = i To i + j - 1
If k <> l Then
Range("G" & k).comment.Text Text:=Range("G" & k).comment.Text & _
Chr(10) & Range("E" & l).Value & _
" " & Range("L" & l).Value
End If
Next l
Next k
i = i + j - 1
Set myRng = Range("G6", "G" & n1) 'sets the range in myRng
Application.StatusBar = "Adjusting the size of the comments"
'below it sets the size on each comment to autosize
For Each mycell In myRng.Cells
If Not (mycell.comment Is Nothing) Then
With mycell.comment
.Shape.TextFrame.AutoSize = True
End With
End If
Next mycell
in the upper loop i tried: Range("G" & k).Comment.Shape.TextFrame.AutoSize = True
but it doesnt work.
it feels so stupid to have a code that first creates the comment then leaves it, and a few seconds later it has to find the comment again and adjust the size of the comment.