bulk transfer of comments using a range
 

I'm trying to add comments to a range rather than one by one as follows...

Range(Cells(lRow, lOrigCol), Cells(lRow, lCol)).Comment = aRowComment

....in an effort to get better run times but get the following error

object variable or with block variable not set

....when I append .Text on the end of the command I get a compile error
saying assignment to a constant not permitted

aRowComment is an array of comments, built similarly to the array of values
currently transferring to the range with no problems.

bulk transfer of comments using a range
 

I think you're going to have to loop through the cells and loop through the
comments.

Dim aRowComment As String
Dim myCell As Range
dim cCtr as long
dim aRowComment as variant

arowcomment = array("test","test2","test3")

cctr = lbound(arowcomment)
With ActiveSheet
with .Range(.Cells(lRow, lOrigCol), .Cells(lRow, lCol))
if .cells.count < ubound(arowcomment) - lbound(arowcomment) + 1 then
'not enough comments for all the cells!
exit sub
end if

'clear existing comments
.ClearComments
For Each myCell In .Cells
myCell.AddComment Text:=aRowComment(cctr)
cctr = cctr + 1
Next myCell
End With
End With

I have no idea if this puts the comments where you want them--test it before you
trust it.

You may want to loop through each row--or loop through each column.



DB042188 wrote:

I'm trying to add comments to a range rather than one by one as follows...

Range(Cells(lRow, lOrigCol), Cells(lRow, lCol)).Comment = aRowComment

...in an effort to get better run times but get the following error

object variable or with block variable not set

...when I append .Text on the end of the command I get a compile error
saying assignment to a constant not permitted

aRowComment is an array of comments, built similarly to the array of values
currently transferring to the range with no problems.

--

Dave Peterson

bulk transfer of comments using a range
 

Dave, I'm trying to speed things up, is your solution faster than what we
already do which is to add them one by one as they come up thru a call to
something like ...

Private Sub InsertComment(lCol As Long, lRow As Long, sType As String,
sValue As String, lColor As Long)
Dim sTmpCell As String
Dim sComment As String

Const PROC_NAME = "InsertComment"

On Error GoTo ERR_HANDLER

sTmpCell = GetCellAddress(lCol, lRow)

With Range(sTmpCell)
If .Comment Is Nothing Then
.AddComment
.Comment.Text Text:=sType & sValue & vbNullString
Else
sComment = .Comment.Text
.Comment.Text Text:=sComment & vbLf & sType & sValue & vbNullString
End If
.Comment.Visible = False
.Comment.Shape.TextFrame.Characters.Font.Size = 8
.Comment.Shape.TextFrame.Characters.Font.Bold = False
.Comment.Shape.TextFrame.Characters.Font.Color = vbBlue
End With

Exit Sub

ERR_HANDLER:
Call AppError(err, MODULE_NAME, PROC_NAME)

End Sub

?


"Dave Peterson" wrote:

I think you're going to have to loop through the cells and loop through the
comments.

Dim aRowComment As String
Dim myCell As Range
dim cCtr as long
dim aRowComment as variant

arowcomment = array("test","test2","test3")

cctr = lbound(arowcomment)
With ActiveSheet
with .Range(.Cells(lRow, lOrigCol), .Cells(lRow, lCol))
if .cells.count < ubound(arowcomment) - lbound(arowcomment) + 1 then
'not enough comments for all the cells!
exit sub
end if

'clear existing comments
.ClearComments
For Each myCell In .Cells
myCell.AddComment Text:=aRowComment(cctr)
cctr = cctr + 1
Next myCell
End With
End With

I have no idea if this puts the comments where you want them--test it before you
trust it.

You may want to loop through each row--or loop through each column.



DB042188 wrote:

I'm trying to add comments to a range rather than one by one as follows...

Range(Cells(lRow, lOrigCol), Cells(lRow, lCol)).Comment = aRowComment

...in an effort to get better run times but get the following error

object variable or with block variable not set

...when I append .Text on the end of the command I get a compile error
saying assignment to a constant not permitted

aRowComment is an array of comments, built similarly to the array of values
currently transferring to the range with no problems.

--

Dave Peterson

bulk transfer of comments using a range
 

They look pretty much equivalent to me. I would suspect that you wouldn't
notice a difference.

DB042188 wrote:

Dave, I'm trying to speed things up, is your solution faster than what we
already do which is to add them one by one as they come up thru a call to
something like ...

Private Sub InsertComment(lCol As Long, lRow As Long, sType As String,
sValue As String, lColor As Long)
Dim sTmpCell As String
Dim sComment As String

Const PROC_NAME = "InsertComment"

On Error GoTo ERR_HANDLER

sTmpCell = GetCellAddress(lCol, lRow)

With Range(sTmpCell)
If .Comment Is Nothing Then
.AddComment
.Comment.Text Text:=sType & sValue & vbNullString
Else
sComment = .Comment.Text
.Comment.Text Text:=sComment & vbLf & sType & sValue & vbNullString
End If
.Comment.Visible = False
.Comment.Shape.TextFrame.Characters.Font.Size = 8
.Comment.Shape.TextFrame.Characters.Font.Bold = False
.Comment.Shape.TextFrame.Characters.Font.Color = vbBlue
End With

Exit Sub

ERR_HANDLER:
Call AppError(err, MODULE_NAME, PROC_NAME)

End Sub

?

"Dave Peterson" wrote:

I think you're going to have to loop through the cells and loop through the
comments.

Dim aRowComment As String
Dim myCell As Range
dim cCtr as long
dim aRowComment as variant

arowcomment = array("test","test2","test3")

cctr = lbound(arowcomment)
With ActiveSheet
with .Range(.Cells(lRow, lOrigCol), .Cells(lRow, lCol))
if .cells.count < ubound(arowcomment) - lbound(arowcomment) + 1 then
'not enough comments for all the cells!
exit sub
end if

'clear existing comments
.ClearComments
For Each myCell In .Cells
myCell.AddComment Text:=aRowComment(cctr)
cctr = cctr + 1
Next myCell
End With
End With

I have no idea if this puts the comments where you want them--test it before you
trust it.

You may want to loop through each row--or loop through each column.



DB042188 wrote:

I'm trying to add comments to a range rather than one by one as follows...

Range(Cells(lRow, lOrigCol), Cells(lRow, lCol)).Comment = aRowComment

...in an effort to get better run times but get the following error

object variable or with block variable not set

...when I append .Text on the end of the command I get a compile error
saying assignment to a constant not permitted

aRowComment is an array of comments, built similarly to the array of values
currently transferring to the range with no problems.

--

Dave Peterson


--

Dave Peterson