macro for extracting comments

[Lochnagar]

Hi,

Apologies in advance, for the length of the post ...it's long, but what I'm trying to do, I hope, is quite simple.

I have a spreadsheet with a column (column A) of data where some of the cells in the column contain comments (i.e. the little boxes with text in that appear when you hover the cursor over the cell containing a comment). I'm wanting to copy the content of the cell's comment into the adjacent cell (i.e. the cell on the same row but in the next column, in this case column B). I've written a macro that seems to work, but only when I execute it a cell at a time (i.e. with no loop)

Sub Macro3()
Dim s as string

On Error Goto ErrorHandler
s = Activecell.Comment.Text
Activecell.Offset(0,1).Value = s

ErrorHandler:
Activecell.Offset(1,0).Select

End Sub

...the problem I'm encountering is when I try and loop the above macro (by inserting a for loop, see below)

Sub Macro3()
Dim s as String
Dim i, j as Integer

i = 10

For j = 1 to i
On Error Goto ErrorHandler
s = Activecell.Comment.Text
Activecell.Offset(0,1).Value = s

ErrorHandler:
Activecell.Offset(1,0).Select
Next

End Sub

...it crashes whenever it hits the second consecutive cell which doesn't contain a comment, with the following window popping up saying

"Object variable or With block variable not set"

...I'm totally stuck as to why the first macro works, but the second doesn't and I was hoping that someone might be able to help shed some light on my problem.

Any help/comments will be greatly appreciated.

Thanks,
Lochnagar

 

[hatstand]

This code will collect all the comments into "ST Comments" sheet. It also gives the name of the sheet it's from, the cell it's from, the cells value and teh actual comment.

Hope it helps.

Sub Make_ST_Comments_List()
'Copies all comments to the ST Comments Sheet
'Application.ScreenUpdating = False


Sheets("ST Comments").Select

Dim commrange As Range
Dim mycell As Range
Dim ws As Worksheet
Dim newwks As Worksheet
Dim i As Long
Set newwks = Sheets("ST Comments")
Worksheets("ST Comments").Range("A:D").Clear

newwks.Range("A1:D1").Value = _
Array("Sheet Name", "Reference", "Value", "Comment")

For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set commrange = ws.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
'Do nothing
Else

i = newwks.Cells(Rows.Count, 1).End(xlUp).Row

For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = mycell.Address
.Cells(i, 3).Value = mycell.Value
.Cells(i, 4).Value = mycell.Comment.Text
End With
Next mycell
End If
Set commrange = Nothing
Next ws

'Format cells for no wrapping, remove line break
newwks.Cells.WrapText = False
newwks.Columns("D:D").Replace What:=Chr(10), _
Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False


Application.ScreenUpdating = True

End Sub

 

[VoG]

Try like this

Sub test()
Dim c As Comment, i As Long
For i = 1 To 10
    On Error Resume Next
    Set c = Range("A" & i).Comment
    On Error GoTo 0
    If Not c Is Nothing Then Range("B" & i).Value = c.Text
Next i
End Sub

 

[daverunt]

I'm not sure about using the error handler that way so try this test if comment exists.

Sub Macro3()
Dim s As String
Dim i, j As Integer
i = 10
For j = 1 To i
If Not ActiveCell.Comment Is Nothing Then
s = ActiveCell.Comment.Text
ActiveCell.Offset(0, 1).Value = s
End If
ActiveCell.Offset(1, 0).Select
Next
End Sub

 

[Lochnagar]

Hi,

Thanks guys for your prompt replies, your help/suggestions are very much appreciated.

Daverunt & VoG your suggestions were spot on, so thanks.

Daverunt - Just out of interest (and for my education) could you just briefly explain/elaborate on why the way I'd used the errorhandler might not be best/work please?

Thanks,
Lochnagar

 

[daverunt]

Hi I don't know that's why I said I'm not sure about using the error handler in that way. It just seems odd to me for the program to be reliant on an error occuring to function. Perhaps it is the norm.

Maybe someone could enlighten me?

 

[Lochnagar]

No worries Daverunt, I was asking just on the off chance - I'm fairly new to using error handling (i.e. as of this morning).

Thanks,
Lochnagar