Read in cell value and comment from 2 selected cells

[Chuck]

Hi,

I am trying to create a macro that when a user to select any 2 cells
will read in the cell values and comments for each cell. Here is what
I have so far.

'read in selected cells
Dim sCell As Range
Dim sCell1, sCell2 As String
Dim sComment1, sComment2 As Comment
Dim i As Integer

For Each sCell In Selection
If i = 0 Then
sCell1 = sCell
sComment1 = sCell.Comment
i = i + 1
Else
sCell2 = sCell
sComment2 = sCell.Comment
End If
Next sCell

The macro will read in each value but does not like the sComment1 =
sCell.Comment. I then tried this.

'read in selected cells
Dim sCell As Range
Dim sCell1, sCell2 As String
Dim sComment1, sComment2 As Comment
Dim i As Integer

For Each sCell In Selection
If i = 0 Then
sCell1 = sCell
sComment1 = ActiveCell.Comment.Text
i = i + 1
Else
sCell2 = sCell
sComment2 = ActiveCell.Comment.Text
End If
Next sCell

This works for sComment1 but I get a run time error for sComment2.

Thanks,

Chuck

 

[paul.robinson]

Hi
Comments are objects, so
sComment2 as Comment

refers to the comment object, which has a text property. You want
sComment2 As String

because sComment2 is a string variable, not a comment object.

The syntax
Dim sComment1, sComment2 As Comment

did not cause a problem because the code is assuming that sComment1 is
a variant variable. This is an "undefined" variable type and is happy
to be a string.

To wrap up, what you really want is

Dim sComment1 as String, sComment2 As String

regards
Paul

 

[Chuck]

Hi
Comments are objects, so
sComment2 as Comment

refers to the comment object, which has a text property. You want
sComment2 As String

because sComment2 is a string variable, not a comment object.

The syntax
Dim sComment1, sComment2 As Comment

did not cause a problem because the code is assuming that sComment1 is
a variant variable. This is an "undefined" variable type and is happy
to be a string.

To wrap up, what you really want is

Dim sComment1 as String, sComment2 As String

regards
Paul










- Show quoted text -

Thanks Paul.

That took care of the error. I'm am still having trouble reading in
both comments. I select 2 cells and can get the cell value, but what
do I have to do to the FOR statement to read in both comments.

Regards,

Chuck

 

[Chuck]

Thanks Paul.

That took care of the error. I'm am still having trouble reading in
both comments. I select 2 cells and can get the cell value, but what
do I have to do to the FOR statement to read in both comments.

Regards,

Chuck- Hide quoted text -

- Show quoted text -

I solved it. I used this code as it worked.


'read in selected cells
Dim sCell As Range
Dim sCell1, sCell2 As String
Dim sComment1, sComment2 As String
Dim i As Integer

For Each sCell In Selection
If i = 0 Then
sCell1 = sCell
sComment1 = sCell.Comment.Text
i = i + 1
Else
sCell2 = sCell
sComment2 = sCell.Comment.Text
End If
Next sCell

Thanks for your help.