Prev: Strip file extension from fiel name with multiple periods. "file.9
Next: prompt password for a worksheet
From: John Keith on 19 Jan 2010 22:37
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
kd0gd(a)juno.com
From: Jacob Skaria on 19 Jan 2010 23:31
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
> kd0gd(a)juno.com
> .
>
From: John Keith on 20 Jan 2010 01:23
On Tue, 19 Jan 2010 20:31:01 -0800, Jacob Skaria
<JacobSkaria(a)discussions.microsoft.com> 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
kd0gd(a)juno.com
 | 
Pages: 1
Prev: Strip file extension from fiel name with multiple periods. "file.9
Next: prompt password for a worksheet