Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
paste comment text to right of cell
i'm trying to paste comment text to the right of the commented cell,
but first i need to insert blank columns in which comments can be pasted. the macro is supposed to select all cells with comments, change the selection all columns with comments, insert a new column to the right of the selections, and paste the comment text (minus username) into the new blank cell. the rest of the macro works, but when i try to insert new columns to the right of the selection i get an error 1004: cannot use that command on overlapping sections. any ideas? here's what i have so far: Sub comment_macro() Dim CommentCells As Range Dim MyCell As Range ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts).Select Set CommentCells = Selection Selection.EntireColumn.Select Selection.Insert Shift:=xlToLeft CommentCells.Select For Each MyCell In CommentCells MyCell.Offset(0, 1).Value = Mid$(MyCell.Comment.Text, InStr(MyCell.Comment.Text, ":") + 1) Next MyCell End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
paste comment text to right of cell
Try this with Offset
Range("A1").CurrentRegion.Select Selection.SpecialCells(xlCellTypeComments).Select Selection.Offset(0, 1).EntireColumn.Insert -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... i'm trying to paste comment text to the right of the commented cell, but first i need to insert blank columns in which comments can be pasted. the macro is supposed to select all cells with comments, change the selection all columns with comments, insert a new column to the right of the selections, and paste the comment text (minus username) into the new blank cell. the rest of the macro works, but when i try to insert new columns to the right of the selection i get an error 1004: cannot use that command on overlapping sections. any ideas? here's what i have so far: Sub comment_macro() Dim CommentCells As Range Dim MyCell As Range ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts).Select Set CommentCells = Selection Selection.EntireColumn.Select Selection.Insert Shift:=xlToLeft CommentCells.Select For Each MyCell In CommentCells MyCell.Offset(0, 1).Value = Mid$(MyCell.Comment.Text, InStr(MyCell.Comment.Text, ":") + 1) Next MyCell End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
paste comment text to right of cell
Ah, offset! That's what i needed to insert the columns in the right
place... Unfortunately, I still can't actually insert anything - it's still giving me the "overlapping selections" error. I do have multiple comments in many columns - is that what's causing the error? If so, how do I fix it? Instead of selecting all comments, should I try searching each column for comments and then offsetting and inserting? I'm making up syntax and functions as I go, but something like, For Each MyColumn in ActiveSheet If MyColumn.SpecialCells(xlCellTypeComments).Count 1, MyColumn.Select Selection.Offset(0, 1).EntireColumn.Insert Next MyColumn I think there's an AreaCount function that does this, but I'm not sure exactly how. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
paste comment text to right of cell
Try this for 100 columns
Sub test() Dim a As Integer Dim Ccount As Long For a = 100 To 1 Step -1 On Error Resume Next Ccount = Columns(a).SpecialCells(xlCellTypeComments).Count If Err.Number 0 Then Err.Clear Else Columns(a).Offset(0, 1).EntireColumn.Insert End If On Error GoTo 0 Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ps.com... Ah, offset! That's what i needed to insert the columns in the right place... Unfortunately, I still can't actually insert anything - it's still giving me the "overlapping selections" error. I do have multiple comments in many columns - is that what's causing the error? If so, how do I fix it? Instead of selecting all comments, should I try searching each column for comments and then offsetting and inserting? I'm making up syntax and functions as I go, but something like, For Each MyColumn in ActiveSheet If MyColumn.SpecialCells(xlCellTypeComments).Count 1, MyColumn.Select Selection.Offset(0, 1).EntireColumn.Insert Next MyColumn I think there's an AreaCount function that does this, but I'm not sure exactly how. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to paste text into a comment box using toolbar commands | New Users to Excel | |||
how do I paste excel text into a comment box? | Excel Discussion (Misc queries) | |||
Can I paste information (from several cells) into a cell comment? | Excel Discussion (Misc queries) | |||
Remove text from Comment and paste into cell | Excel Programming | |||
a comment plugin & copy paste directly from excel to comment ? fr. | Excel Worksheet Functions |