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

On Jun 18, 2:22*pm, Chuck wrote:
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

On Jun 18, 9:53*am, wrote:
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

On Jun 18, 2:22*pm, Chuck wrote:



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- Hide quoted text -

- 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

On Jun 18, 10:24*am, Chuck wrote:
On Jun 18, 9:53*am, wrote:





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

On Jun 18, 2:22*pm, Chuck wrote:

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- Hide quoted text -

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