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 Befo=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

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

Shilps wrote:

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 Befo=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

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" wrote:

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

Shilps wrote:

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 Befo=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

Does this mean the code doesn't work?

Shilps wrote:

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" wrote:

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

Shilps wrote:

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 Befo=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



--

Dave Peterson