Fecthing comment text

[Guest]

Hi,

I have this piece of code. The objective is to make a copy of 'Ratings'
sheet and transfer all the comments in that sheet as cell values in '
Comments' worksheet

----------------------------------------------------------
Option Explicit

Sub Fetch_comment()
Dim x, y As Integer
Sheets.Add Before:=Worksheets(Worksheets.Count), Type:=xlWorksheet
ActiveSheet.Name = "Comments"
Worksheets("Ratings").Range("1:2").Copy Destination:= _
Worksheets("Comments").Range("A1")
Worksheets("Ratings").Range("A:A").Copy Destination:= _
Worksheets("Comments").Range("A1")
For x = 3 To 100
For y = 2 To 156
Worksheets("Comments").Cells(y, x).Value = _
Worksheets("Ratings").Cells(y, x).Comment.Text
Next
Next
End Sub

-------------------------------------------------------
There are 2 problems

1)After creating the new sheet, if I use this code
Worksheets(Worksheets(Worksheets.Count)).Name = "Comments"
for naming the newly created sheet, it gives Error 13- Type mistmatch. Why
is it giving that error?
2) It is giving Run Time Error 91- Object Variable or With Block Variable
not set at line
Worksheets("Comments").Cells(y, x).Value = _
Worksheets("Ratings").Cells(y, x).Comment.Text

Please tell me what is the error in this code.
TIA
Shilps

 

[Dave Peterson]

If you select the Ratings worksheet and hit Edit|goto|special, you can select
just the cells with comments.

You can use that feature in your code. So you don't have to look at all the
cells without comments--just the ones that have them!


Option Explicit
Sub Fetch_comment2()

Dim ComWks As Worksheet
Dim RateWks As Worksheet
Dim ComRng As Range
Dim myCell As Range

Set RateWks = Worksheets("ratings")

With RateWks
On Error Resume Next
Set ComRng = .Range("b3", .Cells.SpecialCells(xlCellTypeLastCell)) _
.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If ComRng Is Nothing Then
'no comments
Exit Sub
End If

Sheets.Add Before:=Worksheets(Worksheets.Count), Type:=xlWorksheet
Set ComWks = ActiveSheet
ComWks.Name = "Comments"

.Range("1:2").Copy _
Destination:=ComWks.Range("A1")
.Range("A:A").Copy _
Destination:=ComWks.Range("A1")

For Each myCell In ComRng.Cells
ComWks.Range(myCell.Address).Value _
= myCell.Comment.Text
Next myCell
End With

End Sub

ps. When you did this:

Dim x, y As Integer

You actually declared Y as an integer, but X was declared as a Variant.

Dim x as long, y as long

(I like long's!)

would be one way to fix this.

 

[Guest]

Hi Dave,

The sheet is huge and the objective is not viewing the comments but fetching
those comments and storing them as cell values instead of comments. Plus this
sheet is dynamic, so the idea is to run the sub and fect all the comments .
- Shilps

 

[Dave Peterson]

Does this mean the code doesn't work?

Hi Dave,

The sheet is huge and the objective is not viewing the comments but fetching
those comments and storing them as cell values instead of comments. Plus this
sheet is dynamic, so the idea is to run the sub and fect all the comments .
- Shilps

 

[Guest]

Hi Dave,
As I wrote earlier, the code is giving earlier( description of error is in
my first post of this thread)
Shilps

 

[Dave Peterson]

This line:
Worksheets(Worksheets(Worksheets.Count)).Name = "Comments"

should probably be:
Worksheets(Worksheets.Count).Name = "Comments"

worksheets(worksheets.count) returns the last worksheet in the workbook.

======
this line:
Worksheets("Comments").Cells(y, x).Value = _
Worksheets("Ratings").Cells(y, x).Comment.Text

will only work if:
worksheets("ratings").cells(y,x) actually has a comment.

You could either check each cell to see if it has a comment before you plop the
comment text into the new sheet or you could just limit your loop to the cells
that have comments.

Did you look at the code I suggested?

Did you try it?