Read in cell value and comment from 2 selected cells
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.
|