Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
visual basic macro help
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
visual basic macro help
Here's what I have so far:
Sub comment_macro() Dim CommentCell As Range ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts).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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
visual basic macro help
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 wrote in message oups.com... Here's what I have so far: Sub comment_macro() Dim CommentCell As Range ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts).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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I run Visual Basic procedure using Excel Visual Basic editor? | Excel Programming | |||
Visual Basic Macro | Excel Discussion (Misc queries) | |||
Visual Basic Macro For Solver | Excel Programming | |||
Interpolation Visual Basic Macro!!!! | Excel Programming | |||
visual basic macro in excel | Excel Programming |