PDA

View Full Version : comment autosize help



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.

Bob Phillips
03-25-2008, 10:06 AM
This seems to work fine for me



For k = i To i + j - 1
For l = i To i + j - 1
If k <> l Then
With Range("G" & k).Comment
.Text Text:=Range("G" & k).Comment.Text & _
Chr(10) & Range("E" & l).Value & _
" " & Range("L" & l).Value
.Shape.TextFrame.AutoSize = True
End With
End If
Next l
Next k

Ago
03-25-2008, 10:53 AM
offcourse!
thanks alot!

the strange thing is that the code is much slower now than it was before the change.

first i noticed that it autosized every time it made a new entry in the comment so i moved the with-autosize part to between next l and next k.

but it still was about one minute slower.
strange.

without the change it takes about 10 seconds to do the whole script, after 1 minute +