Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

Including blank rows in a series of comments (sugarthebeet)

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Sugarthebeet - 24 Jan 2006 14:48 GMT
I would like to exclude all comments except those in column G and include
blank cells too.  Can someone help me tweak this code?

Sub PrintCommentsByColumn()

 Dim cell As Range
 Dim myrange As Range, myrangeC As Range
 Dim col As Long
 Dim RowOS As Long
 Dim wsSource As Worksheet
 Dim wsNew As Worksheet
 If ActiveSheet.Comments.Count = 0 Then
   MsgBox "No comments in entire sheet"
   Exit Sub
 End If
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual 'xl95 uses xlManual
 Set wsSource = ActiveSheet
 Sheets.Add
 Set wsNew = ActiveSheet
 wsSource.Activate
 With wsNew.Columns("A:C")
     .VerticalAlignment = xlTop
     .WrapText = True
 End With
 wsNew.Columns("B").ColumnWidth = 15
 wsNew.Columns("C").ColumnWidth = 60
 wsNew.PageSetup.PrintGridlines = True
 RowOS = 2
 wsNew.Cells(1, 3) = "'" & Application.ActiveWorkbook.FullName & " -- " & _
     Application.ActiveSheet.Name
 For col = 1 To ActiveSheet.UsedRange.Columns.Count
    Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _
        Cells.SpecialCells(xlCellTypeComments))
    If myrangeC Is Nothing Then GoTo nxtCol
    For Each cell In myrangeC
       If Trim(cell.Comment.Text) <> "" Then
          RowOS = RowOS + 1
          wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":"
          wsNew.Cells(RowOS, 2) = "'" & cell.Text
          wsNew.Cells(RowOS, 3) = "'" & cell.Comment.Text
       End If
    Next cell
nxtCol:
 Next col
 wsNew.Activate
 Application.Calculation = xlCalculationAutomatic 'xl95 uses xlAutomatic
 Application.ScreenUpdating = True
End Sub
Tom Ogilvy - 24 Jan 2006 15:07 GMT
Sub PrintCommentsByColumn()

 Dim cell As Range
 Dim myrange As Range, myrangeC As Range
 Dim col As Long
 Dim RowOS As Long
 Dim wsSource As Worksheet
 Dim wsNew As Worksheet
 If ActiveSheet.Comments.Count = 0 Then
   MsgBox "No comments in entire sheet"
   Exit Sub
 End If
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual 'xl95 uses xlManual
 Set wsSource = ActiveSheet
 Sheets.Add
 Set wsNew = ActiveSheet
 wsSource.Activate
 With wsNew.Columns("A:C")
     .VerticalAlignment = xlTop
     .WrapText = True
 End With
 wsNew.Columns("B").ColumnWidth = 15
 wsNew.Columns("C").ColumnWidth = 60
 wsNew.PageSetup.PrintGridlines = True
 RowOS = 2
 wsNew.Cells(1, 3) = "'" & Application.ActiveWorkbook.FullName & " -- " & _
     Application.ActiveSheet.Name
     col = "G"
     On Error Resume Next
     Set myrangeC = Columns(col).Cells.SpecialCells(xlCellTypeComments))
     On Error goto 0
    If myrangeC Is Nothing Then Exit sub
    For Each cell In myrangeC
       If Trim(cell.Comment.Text) <> "" Then
          RowOS = RowOS + 1
          wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":"
          wsNew.Cells(RowOS, 2) = "'" & cell.Text
          wsNew.Cells(RowOS, 3) = "'" & cell.Comment.Text
       End If
    Next cell
 wsNew.Activate
 Application.Calculation = xlCalculationAutomatic 'xl95 uses xlAutomatic
 Application.ScreenUpdating = True
End Sub

Signature

Regards,
Tom Ogilvy

> I would like to exclude all comments except those in column G and include
> blank cells too.  Can someone help me tweak this code?
[quoted text clipped - 45 lines]
>   Application.ScreenUpdating = True
> End Sub
Sugarthebeet - 24 Jan 2006 15:57 GMT
Thank Tom, but the VB editor doesn't seem to like this line:

Set myrangeC = Columns(col).Cells.SpecialCells(xlCellTypeComments))

Compile Error - Syntax Error

Know what to do?

SB

> I would like to exclude all comments except those in column G and include
> blank cells too.  Can someone help me tweak this code?
[quoted text clipped - 45 lines]
>   Application.ScreenUpdating = True
> End Sub
Tom Ogilvy - 24 Jan 2006 16:08 GMT
I edited your code and failed to remove the extra Right paren on the end.

Signature

Regards,
Tom Ogilvy

> Thank Tom, but the VB editor doesn't seem to like this line:
>
[quoted text clipped - 55 lines]
> >   Application.ScreenUpdating = True
> > End Sub
Sugarthebeet - 24 Jan 2006 16:21 GMT
Hi Tom,

Actually I get a runtime error '13' even after deleting that extra bracket.

What do you think?

> I would like to exclude all comments except those in column G and include
> blank cells too.  Can someone help me tweak this code?
[quoted text clipped - 45 lines]
>   Application.ScreenUpdating = True
> End Sub
Tom Ogilvy - 24 Jan 2006 18:43 GMT
A type mismatch error?

So what line of code is highlighted.   What are the values of the variables
in that line of code when you get the error?

Signature

Regards,
Tom Ogilvy

> Hi Tom,
>
[quoted text clipped - 51 lines]
> >   Application.ScreenUpdating = True
> > End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.