visual basic macro help

[shadowsong]

i need a visual basic macro that will select all columns containing
comments and then insert an empty column to the right of each selected
column. any help? i found this code:

Dim iColumn As Long
On Error Resume Next
iColumn = Application.Match("Duration", Rows(1), 0)
On Error GoTo 0
If iColumn > 0 Then
Columns(iColumn).Select
End If

for selecting columns that have "duration" in row 1, and this code:

Dim CommRange As Range
Dim MyCell As Range
Dim CurWks As Worksheet
Set CurWks = ActiveSheet
On Error Resume Next
Set CommRange = CurWks.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0

for selecting cells with comments.


i'm not sure how to select columns with comments, though, and i'm
shooting in the dark for inserting colums to the right of the
selections... maybe add "Selection.EntireColumn.Select" after the
..specialcells() bit to change the cell selections to column selections?
either Selection.Entirecolumn.Insert or Selection.Insert
Shift:=xlToRight to insert the columns? i would guess that the second
option will insert to the right instead of to the left, but i'm just
guessing.

as you may have noticed, i've never used visual basic before, so i'm
probably overlooking something obvious.

 

[shadowsong]

Here's what I have so far:

Sub comment_macro()

Dim CommentCell As Range

ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments).Select

Set CommentCell = Selection

Selection.EntireColumn.Select
Selection.EntireColumn.Insert Shift:=xlToLeft

CommentCell.Select

For Each CommentCell In ActiveSheet.Cells
* CommentCell.Offset(0, 1).Value = CommentCell.Comment.Text
Next CommentCell

End Sub

i'm getting an "object variable not set" error on the asterisked line.
did i forget to define something, or should i have set CommentCell as
an Object instead of a Range?

 

[Conan Kelly]

shadowsong,

I can't be sure, but I think your problem might be in your "For Each..." statement/loop. I would try something like this

For Each c in CommentCell
c.Offset(0, 1).Value = c.Comment.Text
Next c

If you have "Option Explicit" in General Declarations, you will need to declare "c" as a range variable. You can change "c" to
anything that is more meaningful to you.

I hope this helps,

Conan Kelly