Mass Adding Comments
 

I use arrays to fill ranges with values all the time because is it is so fast.

targetRange = myarray

Is there any equivalent (or alternative method) for adding a large number of
comments without having to loop through every location in the target range.

It would be nice if the following worked but it doesn't

targetRange.text = myarray

Andrew

Mass Adding Comments
 

hi,
yes. see this site.
http://www.contextures.com/xlcomments03.html#Name
you may have to scrool. I haven't been in Debra's site in a while but she's
good on comments.
I would guess that you should be able to loop through the range, adding a
plain comments but you may have to use inputbox to type in the comment.

Good luck
FSt1

"Andrew Hall NZ" wrote:

I use arrays to fill ranges with values all the time because is it is so fast.

targetRange = myarray

Is there any equivalent (or alternative method) for adding a large number of
comments without having to loop through every location in the target range.

It would be nice if the following worked but it doesn't

targetRange.text = myarray

Andrew

Mass Adding Comments
 

Hi Andrew -

Although the AddComment method does add text to a cell's comment, my testing
demonstrated to me that each cell must be assigned a comment explicitly; the
'array-to-range' batch assignment (that you described) that works so well for
cell contents does not appear to work with the AddComment method.

So, the code below assigns comments cell-by-cell. It also senses the
'shape' of the selected target range. You will have to do two things to make
sure it works for you.

First, insert code to fill your own array as desired at the beginning of the
procedure.

Second, you'll have to verify that the comments are hitting the cells as you
intend. Because there is no way to know how you are loading your array (row
by column or column by row), you must test the code and, if necessary, either
invert your array elements or change the indices of the for...next loops in
the code below.

Good luck and let us know if it needs modification...
---------------------------------------------------------------------------------------------
Option Base 1

Sub hallNZ()
'------------------------------------------------------------------
'This code will handle one or two dimension arrays
'------------------------------------------------------------------
'Dimension your array here and name it "cellComments()" or replace
'cellComments with yourArrayName in all of the code that follows...

'dim cellComments() as string <<===your array bounds (one dimension or two)
'Assign elements to the array here...
'------------------------------------------------------------------

'Determine number of dimensions in array using Chip Pearson's method
On Error Resume Next
' Loop, increasing the dimension index Ndx, until an error occurs.
' An error will occur when Ndx exceeds the number of dimension
' in the array. Return Ndx - 1.
Ndx = 1
Do
Ndx = Ndx + 1
Res = UBound(cellComments, Ndx)
Loop Until Err.Number < 0
NumberOfArrayDimensions = Ndx - 1

if NumberOfArrayDimensions2 then
msgbox "This procedure will only work on one or two dimension arrays.", _
vbinformation
exit sub
end if

Set targetRng = Selection '<<==Replace 'Selection' with yourRangeName
tRRows = targetRng.Rows.Count: tRCols = targetRng.Columns.Count

Select Case NumberOfArrayDimensions
Case 2
'Sense "shape" of target range relative to array dimensions...
If tRRows = UBound(cellComments, 1) And _
tRCols = UBound(cellComments, 2) Then typ = "RowByCol"
If tRRows = UBound(cellComments, 2) And _
tRCols = UBound(cellComments, 1) Then typ = "ColByRow"

'Exit sub if dimensions do not match
If typ = "" Then
MsgBox "Target Range dimensions do not match array dimensions."
Exit Sub
End If

'Dimensions match; prepare worksheet range by deleting existing comments
'to avoid error produced by adding comments to a pre-commented cell.
On Error Resume Next
For Each itm In targetRng.Cells
itm.Comment.Delete
Next 'itm
On Error GoTo 0

irow = targetRng.Row - 1: jcol = targetRng.Column - 1
Select Case typ

Case "RowByCol"
For i = 1 To UBound(cellComments, 1)
For j = 1 To UBound(cellComments, 2)
Cells(irow + i, jcol + j).AddComment cellComments(i, j)
Next j
Next i

Case "ColByRow"
For j = 1 To UBound(cellComments, 1)
For i = 1 To UBound(cellComments, 2)
Cells(irow + i, jcol + j).AddComment cellComments(j, i)
Next i
Next j
End Select
Exit Sub

Case 1
If tRRows < UBound(cellComments, 1) And _
tRCols < UBound(cellComments, 1) Then
'Exit sub if dimensions do not match
MsgBox "Target Range dimensions do not match array dimensions."
Exit Sub
End If

If tRCols tRRows Then
For j = 1 To UBound(cellComments, 1)
Cells(irow + 1, jcol + j).AddComment cellComments(j)
Next j
Else
For i = 1 To UBound(cellComments, 1)
Cells(irow + i, jcol + 1).AddComment cellComments(i)
Next i
End If

End Select

End Sub

--
Jay


"Andrew Hall NZ" wrote:

I use arrays to fill ranges with values all the time because is it is so fast.

targetRange = myarray

Is there any equivalent (or alternative method) for adding a large number of
comments without having to loop through every location in the target range.

It would be nice if the following worked but it doesn't

targetRange.text = myarray

Andrew

Mass Adding Comments
 

Wow Jay, you did a lot of work there, thanks - the question was basically
conceptual, as you answered in the first few lines. If I do come across a
faster way to do this I will report back but it looks like element by element
it has to be.

Cheers

Andrew

"Jay" wrote:

Hi Andrew -

Although the AddComment method does add text to a cell's comment, my testing
demonstrated to me that each cell must be assigned a comment explicitly; the
'array-to-range' batch assignment (that you described) that works so well for
cell contents does not appear to work with the AddComment method.

So, the code below assigns comments cell-by-cell. It also senses the
'shape' of the selected target range. You will have to do two things to make
sure it works for you.

First, insert code to fill your own array as desired at the beginning of the
procedure.

Second, you'll have to verify that the comments are hitting the cells as you
intend. Because there is no way to know how you are loading your array (row
by column or column by row), you must test the code and, if necessary, either
invert your array elements or change the indices of the for...next loops in
the code below.

Good luck and let us know if it needs modification...
---------------------------------------------------------------------------------------------
Option Base 1

Sub hallNZ()
'------------------------------------------------------------------
'This code will handle one or two dimension arrays
'------------------------------------------------------------------
'Dimension your array here and name it "cellComments()" or replace
'cellComments with yourArrayName in all of the code that follows...

'dim cellComments() as string <<===your array bounds (one dimension or two)
'Assign elements to the array here...
'------------------------------------------------------------------

'Determine number of dimensions in array using Chip Pearson's method
On Error Resume Next
' Loop, increasing the dimension index Ndx, until an error occurs.
' An error will occur when Ndx exceeds the number of dimension
' in the array. Return Ndx - 1.
Ndx = 1
Do
Ndx = Ndx + 1
Res = UBound(cellComments, Ndx)
Loop Until Err.Number < 0
NumberOfArrayDimensions = Ndx - 1

if NumberOfArrayDimensions2 then
msgbox "This procedure will only work on one or two dimension arrays.", _
vbinformation
exit sub
end if

Set targetRng = Selection '<<==Replace 'Selection' with yourRangeName
tRRows = targetRng.Rows.Count: tRCols = targetRng.Columns.Count

Select Case NumberOfArrayDimensions
Case 2
'Sense "shape" of target range relative to array dimensions...
If tRRows = UBound(cellComments, 1) And _
tRCols = UBound(cellComments, 2) Then typ = "RowByCol"
If tRRows = UBound(cellComments, 2) And _
tRCols = UBound(cellComments, 1) Then typ = "ColByRow"

'Exit sub if dimensions do not match
If typ = "" Then
MsgBox "Target Range dimensions do not match array dimensions."
Exit Sub
End If

'Dimensions match; prepare worksheet range by deleting existing comments
'to avoid error produced by adding comments to a pre-commented cell.
On Error Resume Next
For Each itm In targetRng.Cells
itm.Comment.Delete
Next 'itm
On Error GoTo 0

irow = targetRng.Row - 1: jcol = targetRng.Column - 1
Select Case typ

Case "RowByCol"
For i = 1 To UBound(cellComments, 1)
For j = 1 To UBound(cellComments, 2)
Cells(irow + i, jcol + j).AddComment cellComments(i, j)
Next j
Next i

Case "ColByRow"
For j = 1 To UBound(cellComments, 1)
For i = 1 To UBound(cellComments, 2)
Cells(irow + i, jcol + j).AddComment cellComments(j, i)
Next i
Next j
End Select
Exit Sub

Case 1
If tRRows < UBound(cellComments, 1) And _
tRCols < UBound(cellComments, 1) Then
'Exit sub if dimensions do not match
MsgBox "Target Range dimensions do not match array dimensions."
Exit Sub
End If

If tRCols tRRows Then
For j = 1 To UBound(cellComments, 1)
Cells(irow + 1, jcol + j).AddComment cellComments(j)
Next j
Else
For i = 1 To UBound(cellComments, 1)
Cells(irow + i, jcol + 1).AddComment cellComments(i)
Next i
End If

End Select

End Sub

--
Jay


"Andrew Hall NZ" wrote:

I use arrays to fill ranges with values all the time because is it is so fast.

targetRange = myarray

Is there any equivalent (or alternative method) for adding a large number of
comments without having to loop through every location in the target range.

It would be nice if the following worked but it doesn't

targetRange.text = myarray

Andrew