copying cell comment from one sheet to another
 

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

copying cell comment from one sheet to another
 

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


--
Jacob


"John Keith" wrote:

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

.

copying cell comment from one sheet to another
 

On Tue, 19 Jan 2010 20:31:01 -0800, Jacob Skaria
wrote:

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