Read in cell value and comment from 2 selected cells

  • Thread starter Thread starter Chuck
  • Start date Start date
C

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
 
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
 
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 -
Click to expand...

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
 
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 -
Click to expand...


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.
 
Back
Top