VBA read cell comments

Kavy

Well-known Member
Joined
Jun 25, 2007
Messages
607
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
 
Upvote 0
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:

Code:
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
 
Upvote 0
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:

Code:
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!
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,211,679
Messages
6,103,245
Members
447,850
Latest member
thebuzzman15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top