Pulling out comments with macro

[dwake]

okay, so I am looking at a spreadsheet (not created by me) and I need to pull
out some information in the spreadhseet. The problem is they put all the
information in comment field instead of putting them in rows. the
spreadsheet is built with each column being 1 month, and when there is an
action done in that month, they put all the information in the comments
field. The comments field is standardized. Can I create something to go in
and pull all the information out of each comments field that is spread across
4 years?

 

[Jim Thomlinson]

A macro will do it...

Public Sub WhatEver()
Dim rngComments As Range
Dim rng As Range

On Error Resume Next
Set rngComments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If Not rngComments Is Nothing Then
For Each rng In rngComments
MsgBox rng.Address & vbCrLf & rng.Comment.Text
Next rng
End If
End Sub

 

[Gord Dibben]

Pull out to where?

This macro will place the contents of all comments from all sheets on a new
worksheet.

Sub ListComms()
Dim Cell As Range
Dim sh As Worksheet
Dim csh As Worksheet
Set csh = ActiveWorkbook.Worksheets.Add
csh.Name = "Comments"
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> csh.Name Then
For Each Cell In sh.UsedRange
If Not Cell.Comment Is Nothing Then
With csh.Range("a65536").End(xlUp).Offset(1, 0)
.Value = sh.Name & " " & Cell.Address
.Offset(0, 1).Value = Cell.Comment.text
End With
End If
Next Cell
End If
Next sh
End Sub

The following macro will copy comment text to the cell to the right, if that
cell is empty.

Sub ShowCommentsNextCell()
'based on code posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

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

If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If

For Each mycell In commrange
If mycell.Offset(0, 1).Value = "" Then
mycell.Offset(0, 1).Value = mycell.Comment.Text
End If
Next mycell

Application.ScreenUpdating = True

End Sub


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell.Cells(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Jacob Skaria]

Replace the below part in the same macro suggested by Jim////

If Not rngComments Is Nothing Then
For Each rng In rngComments
MsgBox rng.Address & vbCrLf & rng.Comment.Text
Next rng
End If

with..

Dim lngRow as Long
If Not rngComments Is Nothing Then
For Each rng In rngComments
lngRow = lngRow + 1
Sheets("Comments").Range("A" & lngRow) = rng.Comment.Text
Next rng
End If

This will extract all comments to Col A of a sheet named 'Comments'. Create
a sheet named 'Comments' and try

If this post helps click Yes