|
Fecthing comment text
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 |
Fecthing comment text
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 |
Fecthing comment text
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 |
| All times are GMT +1. The time now is 05:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com