copying cell comment from one sheet to another

[John Keith]

I need to copy a cell value and a comment if it exists from one sheet
to another sheet in the same workbook. I tried the following code but
it fails on the if statement with the error message:

Object variable or With block variable not set

The code works fine if the source cell has a comment and only fails
when the source cell does not have a comment.

What do I need to add to the code to make this work correctly?

Sub copy_cell()
Worksheets("sheet2").Cells(1, 1) = Worksheets("sheet1").Cells(1,
1)
If Worksheets("sheet1").Cells(1, 1).Comment.Text <> "" Then
Worksheets("sheet2").Cells(1, 1).AddComment
Text:=Worksheets("sheet1").Cells(1, 1).Comment.Text
End If
End Sub

Thanks for looking.



John Keith
(e-mail address removed)

 

[Jacob Skaria]

The below will copy the value and comment

Worksheets("sheet1").Cells(1, 1).Copy Worksheets("sheet2").Cells(1, 1)

Modified your macro to suit...

Sub copy_cell()
Worksheets("sheet2").Cells(1, 1) = Worksheets("sheet1").Cells(1, 1)
If Not Worksheets("sheet1").Cells(1, 1).Comment Is Nothing Then
Worksheets("sheet2").Cells(1, 1).AddComment
Text:=Worksheets("sheet1").Cells(1, 1).Comment.Text
End If
End Sub

 

[John Keith]

Modified your macro to suit...

Jacob,

Thank you that worked great.

For my education can you comment on why my original techique failed?


John Keith
(e-mail address removed)