VBA read cell comments

[Kavy]

Good day all,

I am trying to read comments from the an worksheet in VBA. I had it working but I changed something and can't figure out why both lines below fail..any ideas?


If wsR.Cells(rR, rC).Comment.Text <> "" Then

and

wsW.Cells(rw, 3) = wsR.Cells(rR, rC).Comment.Text

The code below works, so all of the variables are set properly
"wsW.Cells(rw, 2) = wsR.Cells(rR, rC)"


error is "Object variable or with block variable not set")

Thank you!

Kavy

 

[pgc01]

Hi Kavy

Did you check if the cell does have a comment?

 

[Kavy]

Hi Kavy

Did you check if the cell does have a comment?

Thank you!

Your right, it fails if no comment, works if a comment is there. When I was testing the syntax in my loop, I of course tested it on a cell with a comment.

So..... I am not good with error handling, how would you or anyone test to see if there is a comment in the cell? This was suppose to be the job of the line If wsR.Cells(rR, rC).Comment.Text <> "" Then

Thanks again

 

[pgc01]

Hi Kavy

3 cases: the cell has no comment, it has a comment but it is a null string, it has a comment with a non null string.

Test and adapt:

Sub test()
Dim s As String

If Not Range("A1").Comment Is Nothing Then
    s = Range("A1").Comment.Text
    If s = "" Then
        MsgBox "Empty comment in A1"
    Else
        MsgBox "Comment in A1: " & s
    End If
Else
    MsgBox "No comment in A1"
End If
End Sub

 

[Kavy]

Hi Kavy

3 cases: the cell has no comment, it has a comment but it is a null string, it has a comment with a non null string.

Test and adapt:

Sub test()
Dim s As String

If Not Range("A1").Comment Is Nothing Then
    s = Range("A1").Comment.Text
    If s = "" Then
        MsgBox "Empty comment in A1"
    Else
        MsgBox "Comment in A1: " & s
    End If
Else
    MsgBox "No comment in A1"
End If
End Sub

Thank you, will use!

I am glad i asked, you didn't use the Error approach!

 

[Kavy]

Thanks all for help, got it to work, my final code is below. If anyone searches this thread and gets an error, be careful, in the line .comment is nothing it is NOT .comment.text it is just .comment (as pg01 did correctly show)

<code>
For rR = wsM.Cells(4, 7) To wsM.Cells(5, 7)

For rC = wsM.Cells(6, 7) To wsM.Cells(7, 7)
If Not wsR.Cells(rR, rC).Comment Is Nothing Then
com = wsR.Cells(rR, rC).Comment.Text
If com <> "" Then
wsW.Cells(rw, 1) = "Row " & rR & " Collumn " & rC
wsW.Cells(rw, 2) = wsR.Cells(rR, rC)
wsW.Cells(rw, 3) = wsR.Cells(rR, rC).Comment.Text
rw = rw + 1
End If
End If
Next rC
Next rR

</code>

 

[pgc01]

You're welcome. Thanks for the feedback.