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

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

.

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