I have been trying to use this macro:
http://www.contextures.com/xlcomment...l#CopyAdjacent
to copy comments into a different cell. I have seen numerious links to this
same macro, but I can't get it to work. I type it into VB just as it is
written, but when I run the macro, I always get the dialog box that says "no
comments found", even though there are comments. Any ideas what I'm doing
wrong?

Hi SL,

Works fine for me.
Are you sure the cells to the right of the ones containing the
comments are blank?

If you take out the lines:
If mycell.Offset(0, 1).Value = "" Then
&
End If

It should overwrite anything found in the cells to the right - any use
to you?

HtH,
JF

On 25 Dec, 10:17, Still Learning
<StillLearn...@discussions.microsoft.com> wrote:
> I have been trying to use this macro:http://www.contextures.com/xlcomment...l#CopyAdjacent
> to copy comments into a different cell. *I have seen numerious links tothis
> same macro, but I can't get it to work. *I type it into VB just as it is
> written, but when I run the macro, I always get the dialog box that says "no
> comments found", even though there are comments. *Any ideas what I'm doing
> wrong?

Okay, I took out the lines you said. I still get the no comments found msg.

Here is what I'm doing to test this;
I highlight the cell that has a comment (with the little red triangle) and
select run macro. That's when I get the msg.

I've also tried running the macro on the sheet, it did the same, that's why
I tried just the one cell.

I very new to programming in VB. I would really like to get this to work.

"Joshua Fandango" wrote:

> Hi SL,
>
> Works fine for me.
> Are you sure the cells to the right of the ones containing the
> comments are blank?
>
> If you take out the lines:
> If mycell.Offset(0, 1).Value = "" Then
> &
> End If
>
> It should overwrite anything found in the cells to the right - any use
> to you?
>
> HtH,
> JF
>
> On 25 Dec, 10:17, Still Learning
> <StillLearn...@discussions.microsoft.com> wrote:
> > I have been trying to use this macro:http://www.contextures.com/xlcomment...l#CopyAdjacent
> > to copy comments into a different cell. I have seen numerious links to this
> > same macro, but I can't get it to work. I type it into VB just as it is
> > written, but when I run the macro, I always get the dialog box that says "no
> > comments found", even though there are comments. Any ideas what I'm doing
> > wrong?
>
>

Is the worksheet protected?

On 25 Dec, 11:05, Still Learning
<StillLearn...@discussions.microsoft.com> wrote:
> Okay, I took out the lines you said. *I still get the no comments foundmsg.
>
> Here is what I'm doing to test this;
> I highlight the cell that has a comment (with the little red triangle) and
> select run macro. *That's when I get the msg.
>
> I've also tried running the macro on the sheet, it did the same, that's why
> I tried just the one cell.
>
> I very new to programming in VB. *I would really like to get this to work.
>
>
>
> "Joshua Fandango" wrote:
> > Hi SL,
>
> > Works fine for me.
> > Are you sure the cells to the right of the ones containing the
> > comments are blank?
>
> > If you take out the lines:
> > If mycell.Offset(0, 1).Value = "" Then
> > &
> > End If
>
> > It should overwrite anything found in the cells to the right - any use
> > to you?
>
> > HtH,
> > JF
>
> > On 25 Dec, 10:17, Still Learning
> > <StillLearn...@discussions.microsoft.com> wrote:
> > > I have been trying to use this macro:http://www.contextures.com/xlcomment...l#CopyAdjacent
> > > to copy comments into a different cell. *I have seen numerious links to this
> > > same macro, but I can't get it to work. *I type it into VB just as it is
> > > written, but when I run the macro, I always get the dialog box that says "no
> > > comments found", even though there are comments. *Any ideas what I'm doing
> > > wrong?- Hide quoted text -
>
> - Show quoted text -

No it is not. I'm assuming that the macro doesn't need anything typed into
it (ie ranges (a1:b4) or sheet names or anythng).

Thanks for trying to help.

PS, is there somewhere that lists what these comands do? (as I said, I'm
still learning)

"Joshua Fandango" wrote:

> Community Message Not Available

Hi SL,

You're right, there's no need to specify a range to act on as the code
works on every cell in the active worksheet with a comment in it (or
it is supposed too!)

See if the following will work; you need to highlight the cells
containing comments before running it.

Sub Comment_Text_In_Cell_To_Right()
Dim Cell As Object
On Error Resume Next
For Each Cell In Selection
If Cell.Comment = True Then
Cell.Offset(0, 1) = Cell.Comment.Text
End If
Next Cell
On Error GoTo 0
End Sub


On 25 Dec, 11:35, Still Learning <Still
Learn...@discussions.microsoft.com> wrote:
> No it is not. *I'm assuming that the macro doesn't need anything typed into
> it (ie ranges (a1:b4) or sheet names or anythng).
>
> Thanks for trying to help.
>
> PS, is there somewhere that lists what these comands do? *(as I said, I'm
> still learning)
>
>
>
> "Joshua Fandango" wrote:
> > Community Message Not Available- Hide quoted text -
>
> - Show quoted text -

Give this macro a try; it is a little more compact than the one posted on
Debra's website and it operates slightly differently also (no error checking
is required with it)...

Sub ShowCommentsNextCell()
Dim C As Comment
If ActiveSheet.Comments.Count > 0 Then
Application.ScreenUpdating = False
For Each C In ActiveSheet.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
Application.ScreenUpdating = True
End If
End Sub

By the way, you should always copy/paste code from newsgroups and/or
websites rather than typing them into the VB code windows (as your original
post indicated you did).

--
Rick (MVP - Excel)


"Still Learning" <Still (E-Mail Removed)> wrote in message
news:3E66F685-D08C-4795-B1BE-(E-Mail Removed)...
> No it is not. I'm assuming that the macro doesn't need anything typed
> into
> it (ie ranges (a1:b4) or sheet names or anythng).
>
> Thanks for trying to help.
>
> PS, is there somewhere that lists what these comands do? (as I said, I'm
> still learning)
>
> "Joshua Fandango" wrote:
>
>> Community Message Not Available

On Dec 26, 12:26*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Give this macro a try; it is a little more compact than the one posted on
> Debra's website and it operates slightly differently also (no error checking
> is required with it)...
>
> Sub ShowCommentsNextCell()
> * Dim C As Comment
> * If ActiveSheet.Comments.Count > 0 Then
> * * Application.ScreenUpdating = False
> * * For Each C In ActiveSheet.Comments
> * * * With C.Parent.Offset(, 1)
> * * * * If .Value = "" Then .Value = C.Text
> * * * End With
> * * Next
> * * Application.ScreenUpdating = True
> * End If
> End Sub
>
> By the way, you should always copy/paste code from newsgroups and/or
> websites rather than typing them into the VB code windows (as your original
> post indicated you did).
>
> --
> Rick (MVP - Excel)
>
> "Still Learning" <Still Learn...@discussions.microsoft.com> wrote in messagenews:3E66F685-D08C-4795-B1BE-(E-Mail Removed)...
>
>
>
> > No it is not. *I'm assuming that the macro doesn't need anything typed
> > into
> > it (ie ranges (a1:b4) or sheet names or anythng).
>
> > Thanks for trying to help.
>
> > PS, is there somewhere that lists what these comands do? *(as I said,I'm
> > still learning)
>
> > "Joshua Fandango" wrote:
>
> >> Community Message Not Available- Hide quoted text -
>
> - Show quoted text -

I think the problem with the original macro was that it only picks up
comments on the current page. so if you apply the macro to a command
button, it will only scan the current worksheet for comments in which
the command button that executed the macro resides. If there are no
comments posted on the current worksheet, the dialogue box will return
"no comments found" even thought there could be several in the
remainder of the workbook.

Try manipulating the language so that it applies to the entire
workbook when executing so that it doesnt just scan an indidual sheet.

hopefully that helps, if not, disregard.

If that it the case, here is my macro modified to copy all comments on all
sheets over to the next cell provided that cell has nothing in it...

Sub ShowCommentsNextCell()
Dim C As Comment
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In Worksheets
If WS.Comments.Count > 0 Then
For Each C In WS.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
End If
Next
Application.ScreenUpdating = True
End Sub

--
Rick (MVP - Excel)


"brian the great" <(E-Mail Removed)> wrote in message
news:ce840ab5-6229-410b-92f5-(E-Mail Removed)...
On Dec 26, 12:26 pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Give this macro a try; it is a little more compact than the one posted on
> Debra's website and it operates slightly differently also (no error
> checking
> is required with it)...
>
> Sub ShowCommentsNextCell()
> Dim C As Comment
> If ActiveSheet.Comments.Count > 0 Then
> Application.ScreenUpdating = False
> For Each C In ActiveSheet.Comments
> With C.Parent.Offset(, 1)
> If .Value = "" Then .Value = C.Text
> End With
> Next
> Application.ScreenUpdating = True
> End If
> End Sub
>
> By the way, you should always copy/paste code from newsgroups and/or
> websites rather than typing them into the VB code windows (as your
> original
> post indicated you did).
>
> --
> Rick (MVP - Excel)
>
> "Still Learning" <Still Learn...@discussions.microsoft.com> wrote in
> messagenews:3E66F685-D08C-4795-B1BE-(E-Mail Removed)...
>
>
>
> > No it is not. I'm assuming that the macro doesn't need anything typed
> > into
> > it (ie ranges (a1:b4) or sheet names or anythng).
>
> > Thanks for trying to help.
>
> > PS, is there somewhere that lists what these comands do? (as I said, I'm
> > still learning)
>
> > "Joshua Fandango" wrote:
>
> >> Community Message Not Available- Hide quoted text -
>
> - Show quoted text -

I think the problem with the original macro was that it only picks up
comments on the current page. so if you apply the macro to a command
button, it will only scan the current worksheet for comments in which
the command button that executed the macro resides. If there are no
comments posted on the current worksheet, the dialogue box will return
"no comments found" even thought there could be several in the
remainder of the workbook.

Try manipulating the language so that it applies to the entire
workbook when executing so that it doesnt just scan an indidual sheet.

hopefully that helps, if not, disregard.

I notice that I did not include the warning message box you mentioned about
the active sheet not having any comments on it; here is my code, modified to
show a message box when there are no comments on the active worksheet...

Sub ShowCommentsNextCell()
Dim C As Comment
If ActiveSheet.Comments.Count > 0 Then
Application.ScreenUpdating = False
For Each C In ActiveSheet.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
Application.ScreenUpdating = True
Else
MsgBox "There are no comments on this sheet."
End If
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Give this macro a try; it is a little more compact than the one posted on
> Debra's website and it operates slightly differently also (no error
> checking is required with it)...
>
> Sub ShowCommentsNextCell()
> Dim C As Comment
> If ActiveSheet.Comments.Count > 0 Then
> Application.ScreenUpdating = False
> For Each C In ActiveSheet.Comments
> With C.Parent.Offset(, 1)
> If .Value = "" Then .Value = C.Text
> End With
> Next
> Application.ScreenUpdating = True
> End If
> End Sub
>
> By the way, you should always copy/paste code from newsgroups and/or
> websites rather than typing them into the VB code windows (as your
> original post indicated you did).
>
> --
> Rick (MVP - Excel)
>
>
> "Still Learning" <Still (E-Mail Removed)> wrote in
> message news:3E66F685-D08C-4795-B1BE-(E-Mail Removed)...
>> No it is not. I'm assuming that the macro doesn't need anything typed
>> into
>> it (ie ranges (a1:b4) or sheet names or anythng).
>>
>> Thanks for trying to help.
>>
>> PS, is there somewhere that lists what these comands do? (as I said, I'm
>> still learning)
>>
>> "Joshua Fandango" wrote:
>>
>>> Community Message Not Available
>