Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
Im working on creating a report that will insert comments from a separate
workbook. Im clear on what I want to achieve, but Im sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. Heres what Ive got so far (but its not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
You have carefully defined both the source and destination ranges. Why not
paste/special comments?: Sub Macro1() Range("D1").Select Selection.Copy Range("F5").Select Selection.PasteSpecial Paste:=xlPasteComments End Sub Where you use your ranges in place of mine. -- Gary's Student gsnu200708 "Jay" wrote: Im working on creating a report that will insert comments from a separate workbook. Im clear on what I want to achieve, but Im sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. Heres what Ive got so far (but its not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
So you have a single workorder number and you want to copy a comment from the
workorder queue that is associated with that workorder number? Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If WkOr.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? WkOr.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: Im working on creating a report that will insert comments from a separate workbook. Im clear on what I want to achieve, but Im sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. Heres what Ive got so far (but its not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
ps. If you ever decide to redesign your workbooks, you may want to move all the
comments to just plain old cells--not comments, just values. It would make things like this easier--just use =vlookup() to retrieve that value. Jay wrote: Im working on creating a report that will insert comments from a separate workbook. Im clear on what I want to achieve, but Im sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. Heres what Ive got so far (but its not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
Thanks for the input, you give a great suggestion.
Where Im getting caught up with is in looking up the particular work order number in a range first before getting the comment information. Im currently exploring Daves suggestions where it looks like he has addressed this issue. Thanks again for your comments! "Gary''s Student" wrote: You have carefully defined both the source and destination ranges. Why not paste/special comments?: Sub Macro1() Range("D1").Select Selection.Copy Range("F5").Select Selection.PasteSpecial Paste:=xlPasteComments End Sub Where you use your ranges in place of mine. -- Gary's Student gsnu200708 "Jay" wrote: Im working on creating a report that will insert comments from a separate workbook. Im clear on what I want to achieve, but Im sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. Heres what Ive got so far (but its not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
Dave, You are a godsend!
Thank you for the clear layout of the code, complete with easy to follow comments. That answers my question precisely. However, your clarification question identifies my next dilemma: So you have a single workorder number you want to copy a comment from Actually, I have a list of work order numbers. How is the best way to proceed down column B of work order numbers and populate the corresponding column D with comments? I was thinking of making this routine into a user defined function that could be copied down a column in Excel (but I havent figured out how to create user defined functions yet). OR is there a simpler or better way to do this within the code itself? "Dave Peterson" wrote: So you have a single workorder number and you want to copy a comment from the workorder queue that is associated with that workorder number? Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If WkOr.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? WkOr.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: Iâm working on creating a report that will insert comments from a separate workbook. Iâm clear on what I want to achieve, but Iâm sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. Hereâs what Iâve got so far (but itâs not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
That is a great suggestion. I started resorting to macros when I discovered
vlookup wouldnt work with comments. "Dave Peterson" wrote: ps. If you ever decide to redesign your workbooks, you may want to move all the comments to just plain old cells--not comments, just values. It would make things like this easier--just use =vlookup() to retrieve that value. Jay wrote: Iâm working on creating a report that will insert comments from a separate workbook. Iâm clear on what I want to achieve, but Iâm sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. Hereâs what Iâve got so far (but itâs not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
First, there were a couple of mistakes in that original code--I cleared the
comments in wkor--not destrng. But since you're dumping that code, it doesn't matter <bg. Anyway, I'd just create a macro to run on demand. It would open up the "sending" workbook and do all the cells in B4 to the last used cell in column B. This is untested, but it did compile--and the destrng stuff is fixed: Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim RngToFix As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If With ThisWorkbook.Worksheets("sheet1") Set RngToFix = .Range("b4", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each WkOr In RngToFix.Cells Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If DestRng.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? DestRng.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If Next WkOr 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: Dave, You are a godsend! Thank you for the clear layout of the code, complete with easy to follow comments. That answers my question precisely. However, your clarification question identifies my next dilemma: So you have a single workorder number you want to copy a comment from Actually, I have a list of work order numbers. How is the best way to proceed down column B of work order numbers and populate the corresponding column D with comments? I was thinking of making this routine into a user defined function that could be copied down a column in Excel (but I havent figured out how to create user defined functions yet). OR is there a simpler or better way to do this within the code itself? "Dave Peterson" wrote: So you have a single workorder number and you want to copy a comment from the workorder queue that is associated with that workorder number? Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If WkOr.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? WkOr.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: Iâm working on creating a report that will insert comments from a separate workbook. Iâm clear on what I want to achieve, but Iâm sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. Hereâs what Iâve got so far (but itâs not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
That worked beautifully! Once again, thank you so much.
Actually I did notice the mistake in the original code and removed that IF statement. I actually do not intend on moving the comments, so I dont see a need to delete the comments in the DestRng. Since the routine closes the workbook without saving it seemed mute anyway (unless there is some other reason for deleting the comment that I dont understand). May I importune your help for one more thing in this macro? The work order can be found in one of either two worksheets from my source workbook, sheet Work Orders or sheet Completed. There is a perfect place in the IF statement to search the second worksheet if there is no match found in the first worksheet, but how do I define the second worksheet in the code? "Dave Peterson" wrote: First, there were a couple of mistakes in that original code--I cleared the comments in wkor--not destrng. But since you're dumping that code, it doesn't matter <bg. Anyway, I'd just create a macro to run on demand. It would open up the "sending" workbook and do all the cells in B4 to the last used cell in column B. This is untested, but it did compile--and the destrng stuff is fixed: Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim RngToFix As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If With ThisWorkbook.Worksheets("sheet1") Set RngToFix = .Range("b4", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each WkOr In RngToFix.Cells Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If DestRng.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? DestRng.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If Next WkOr 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: Dave, You are a godsend! Thank you for the clear layout of the code, complete with easy to follow comments. That answers my question precisely. However, your clarification question identifies my next dilemma: âœSo you have a single workorder number⦠you want to copy a comment fromâ Actually, I have a list of work order numbers. How is the best way to proceed down column âœBâ of work order numbers and populate the corresponding column âœDâ with comments? I was thinking of making this routine into a user defined function that could be copied down a column in Excel (but I havenât figured out how to create user defined functions yet). OR â is there a simpler or better way to do this within the code itself? "Dave Peterson" wrote: So you have a single workorder number and you want to copy a comment from the workorder queue that is associated with that workorder number? Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If WkOr.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? WkOr.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: Iââ¬â¢m working on creating a report that will insert comments from a separate workbook. Iââ¬â¢m clear on what I want to achieve, but Iââ¬â¢m sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. Hereââ¬â¢s what Iââ¬â¢ve got so far (but itââ¬â¢s not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
The DestRng is the cell that gets comment. If there's an existing comment, then
the .addcomment will fail. Since you're only using two ranges, I just created another range. Any more ranges and I think I'd start creating some arrays so I could loop through them. It would make the code a little easier to update since there'd be less "copy/pasting". Untested, but compiled. Option Explicit Sub Copy_Comment() Dim SourceRng1 As Range Dim SourceRng2 As Range Dim SourceRngToUse As Range Dim RngToFix As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng1 = Nothing Set SourceRng2 = Nothing On Error Resume Next Set SourceRng1 = Workbooks.Open(Filename:=mySourceWkbkName, _ ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") Set SourceRng2 = SourceRng1.Parent.Parent _ .Worksheets("Completed").Range("A3:A200") On Error GoTo 0 If SourceRng1 Is Nothing _ Or SourceRng2 Is Nothing Then MsgBox "Something wrong with source ranges!" Exit Sub End If With ThisWorkbook.Worksheets("sheet1") Set RngToFix = .Range("b4", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each WkOr In RngToFix.Cells Set DestRng = WkOr.Offset(0, 2) '2 columns to the right Set SourceRngToUse = SourceRng1 res = Application.Match(WkOr.Value, SourceRngToUse, 0) If IsError(res) Then 'look in other range Set SourceRngToUse = SourceRng2 res = Application.Match(WkOr.Value, SourceRngToUse, 0) End If If DestRng.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? DestRng.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRngToUse(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRngToUse(res).Comment.Text End If End If Next WkOr 'close the sending workbook SourceRngToUse.Parent.Parent.Close savechanges:=False End Sub Jay wrote: That worked beautifully! Once again, thank you so much. Actually I did notice the mistake in the original code and removed that IF statement. I actually do not intend on moving the comments, so I dont see a need to delete the comments in the DestRng. Since the routine closes the workbook without saving it seemed mute anyway (unless there is some other reason for deleting the comment that I dont understand). May I importune your help for one more thing in this macro? The work order can be found in one of either two worksheets from my source workbook, sheet Work Orders or sheet Completed. There is a perfect place in the IF statement to search the second worksheet if there is no match found in the first worksheet, but how do I define the second worksheet in the code? "Dave Peterson" wrote: First, there were a couple of mistakes in that original code--I cleared the comments in wkor--not destrng. But since you're dumping that code, it doesn't matter <bg. Anyway, I'd just create a macro to run on demand. It would open up the "sending" workbook and do all the cells in B4 to the last used cell in column B. This is untested, but it did compile--and the destrng stuff is fixed: Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim RngToFix As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If With ThisWorkbook.Worksheets("sheet1") Set RngToFix = .Range("b4", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each WkOr In RngToFix.Cells Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If DestRng.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? DestRng.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If Next WkOr 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: Dave, You are a godsend! Thank you for the clear layout of the code, complete with easy to follow comments. That answers my question precisely. However, your clarification question identifies my next dilemma: âœSo you have a single workorder number⦠you want to copy a comment fromâ Actually, I have a list of work order numbers. How is the best way to proceed down column âœBâ of work order numbers and populate the corresponding column âœDâ with comments? I was thinking of making this routine into a user defined function that could be copied down a column in Excel (but I havenât figured out how to create user defined functions yet). OR â is there a simpler or better way to do this within the code itself? "Dave Peterson" wrote: So you have a single workorder number and you want to copy a comment from the workorder queue that is associated with that workorder number? Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If WkOr.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? WkOr.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: Iââ¬â¢m working on creating a report that will insert comments from a separate workbook. Iââ¬â¢m clear on what I want to achieve, but Iââ¬â¢m sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. Hereââ¬â¢s what Iââ¬â¢ve got so far (but itââ¬â¢s not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
Once again, Thank you sooooo much!
You make it so easy. I would be working on this problem for days. You've saved me a lot of effort. Thanks. "Dave Peterson" wrote: The DestRng is the cell that gets comment. If there's an existing comment, then the .addcomment will fail. Since you're only using two ranges, I just created another range. Any more ranges and I think I'd start creating some arrays so I could loop through them. It would make the code a little easier to update since there'd be less "copy/pasting". Untested, but compiled. Option Explicit Sub Copy_Comment() Dim SourceRng1 As Range Dim SourceRng2 As Range Dim SourceRngToUse As Range Dim RngToFix As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng1 = Nothing Set SourceRng2 = Nothing On Error Resume Next Set SourceRng1 = Workbooks.Open(Filename:=mySourceWkbkName, _ ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") Set SourceRng2 = SourceRng1.Parent.Parent _ .Worksheets("Completed").Range("A3:A200") On Error GoTo 0 If SourceRng1 Is Nothing _ Or SourceRng2 Is Nothing Then MsgBox "Something wrong with source ranges!" Exit Sub End If With ThisWorkbook.Worksheets("sheet1") Set RngToFix = .Range("b4", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each WkOr In RngToFix.Cells Set DestRng = WkOr.Offset(0, 2) '2 columns to the right Set SourceRngToUse = SourceRng1 res = Application.Match(WkOr.Value, SourceRngToUse, 0) If IsError(res) Then 'look in other range Set SourceRngToUse = SourceRng2 res = Application.Match(WkOr.Value, SourceRngToUse, 0) End If If DestRng.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? DestRng.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRngToUse(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRngToUse(res).Comment.Text End If End If Next WkOr 'close the sending workbook SourceRngToUse.Parent.Parent.Close savechanges:=False End Sub Jay wrote: That worked beautifully! Once again, thank you so much. Actually I did notice the âœmistakeâ in the original code and removed that IF statement. I actually do not intend on âœmovingâ the comments, so I donât see a need to delete the comments in the DestRng. Since the routine closes the workbook without saving it seemed mute anyway (unless there is some other reason for deleting the comment that I donât understand). May I importune your help for one more thing in this macro? The work order can be found in one of either two worksheets from my source workbook, sheet âœWork Ordersâ or sheet âœCompletedâ. There is a perfect place in the IF statement to search the second worksheet if there is no match found in the first worksheet, but how do I define the second worksheet in the code? "Dave Peterson" wrote: First, there were a couple of mistakes in that original code--I cleared the comments in wkor--not destrng. But since you're dumping that code, it doesn't matter <bg. Anyway, I'd just create a macro to run on demand. It would open up the "sending" workbook and do all the cells in B4 to the last used cell in column B. This is untested, but it did compile--and the destrng stuff is fixed: Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim RngToFix As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If With ThisWorkbook.Worksheets("sheet1") Set RngToFix = .Range("b4", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each WkOr In RngToFix.Cells Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If DestRng.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? DestRng.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If Next WkOr 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: Dave, You are a godsend! Thank you for the clear layout of the code, complete with easy to follow comments. That answers my question precisely. However, your clarification question identifies my next dilemma: ââ¬ÅSo you have a single workorder numberââ¬Â¦ you want to copy a comment fromââ¬Â Actually, I have a list of work order numbers. How is the best way to proceed down column ââ¬ÅBââ¬Â of work order numbers and populate the corresponding column ââ¬ÅDââ¬Â with comments? I was thinking of making this routine into a user defined function that could be copied down a column in Excel (but I havenââ¬â¢t figured out how to create user defined functions yet). OR ââ¬âœ is there a simpler or better way to do this within the code itself? "Dave Peterson" wrote: So you have a single workorder number and you want to copy a comment from the workorder queue that is associated with that workorder number? Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If WkOr.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? WkOr.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: IâââšÂ¬Ã¢âžÂ¢m working on creating a report that will insert comments from a separate workbook. IâââšÂ¬Ã¢âžÂ¢m clear on what I want to achieve, but IâââšÂ¬Ã¢âžÂ¢m sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. HereâââšÂ¬Ã¢âžÂ¢s what IâââšÂ¬Ã¢âžÂ¢ve got so far (but itâââšÂ¬Ã¢âžÂ¢s not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Comments problem
Glad it does what you want--and sorry about the buggy first version <bg.
Jay wrote: Once again, Thank you sooooo much! You make it so easy. I would be working on this problem for days. You've saved me a lot of effort. Thanks. "Dave Peterson" wrote: The DestRng is the cell that gets comment. If there's an existing comment, then the .addcomment will fail. Since you're only using two ranges, I just created another range. Any more ranges and I think I'd start creating some arrays so I could loop through them. It would make the code a little easier to update since there'd be less "copy/pasting". Untested, but compiled. Option Explicit Sub Copy_Comment() Dim SourceRng1 As Range Dim SourceRng2 As Range Dim SourceRngToUse As Range Dim RngToFix As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng1 = Nothing Set SourceRng2 = Nothing On Error Resume Next Set SourceRng1 = Workbooks.Open(Filename:=mySourceWkbkName, _ ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") Set SourceRng2 = SourceRng1.Parent.Parent _ .Worksheets("Completed").Range("A3:A200") On Error GoTo 0 If SourceRng1 Is Nothing _ Or SourceRng2 Is Nothing Then MsgBox "Something wrong with source ranges!" Exit Sub End If With ThisWorkbook.Worksheets("sheet1") Set RngToFix = .Range("b4", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each WkOr In RngToFix.Cells Set DestRng = WkOr.Offset(0, 2) '2 columns to the right Set SourceRngToUse = SourceRng1 res = Application.Match(WkOr.Value, SourceRngToUse, 0) If IsError(res) Then 'look in other range Set SourceRngToUse = SourceRng2 res = Application.Match(WkOr.Value, SourceRngToUse, 0) End If If DestRng.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? DestRng.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRngToUse(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRngToUse(res).Comment.Text End If End If Next WkOr 'close the sending workbook SourceRngToUse.Parent.Parent.Close savechanges:=False End Sub Jay wrote: That worked beautifully! Once again, thank you so much. Actually I did notice the âœmistakeâ in the original code and removed that IF statement. I actually do not intend on âœmovingâ the comments, so I donât see a need to delete the comments in the DestRng. Since the routine closes the workbook without saving it seemed mute anyway (unless there is some other reason for deleting the comment that I donât understand). May I importune your help for one more thing in this macro? The work order can be found in one of either two worksheets from my source workbook, sheet âœWork Ordersâ or sheet âœCompletedâ. There is a perfect place in the IF statement to search the second worksheet if there is no match found in the first worksheet, but how do I define the second worksheet in the code? "Dave Peterson" wrote: First, there were a couple of mistakes in that original code--I cleared the comments in wkor--not destrng. But since you're dumping that code, it doesn't matter <bg. Anyway, I'd just create a macro to run on demand. It would open up the "sending" workbook and do all the cells in B4 to the last used cell in column B. This is untested, but it did compile--and the destrng stuff is fixed: Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim RngToFix As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If With ThisWorkbook.Worksheets("sheet1") Set RngToFix = .Range("b4", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each WkOr In RngToFix.Cells Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If DestRng.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? DestRng.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If Next WkOr 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: Dave, You are a godsend! Thank you for the clear layout of the code, complete with easy to follow comments. That answers my question precisely. However, your clarification question identifies my next dilemma: ââ¬ÅSo you have a single workorder numberââ¬Â¦ you want to copy a comment fromââ¬Â Actually, I have a list of work order numbers. How is the best way to proceed down column ââ¬ÅBââ¬Â of work order numbers and populate the corresponding column ââ¬ÅDââ¬Â with comments? I was thinking of making this routine into a user defined function that could be copied down a column in Excel (but I havenââ¬â¢t figured out how to create user defined functions yet). OR ââ¬âœ is there a simpler or better way to do this within the code itself? "Dave Peterson" wrote: So you have a single workorder number and you want to copy a comment from the workorder queue that is associated with that workorder number? Option Explicit Sub Copy_Comment() Dim SourceRng As Range Dim WkOr As Range Dim DestRng As Range Dim res As Variant Dim mySourceWkbkName As String mySourceWkbkName = "H:\FAC\Drafting Work Queue2.xls" Set SourceRng = Nothing On Error Resume Next Set SourceRng = Workbooks.Open(Filename:=mySourceWkbkName, ReadOnly:=True) _ .Worksheets("Work Orders").Range("A3:A200") On Error GoTo 0 If SourceRng Is Nothing Then MsgBox "Something wrong with source range!" Exit Sub End If Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = WkOr.Offset(0, 2) '2 columns to the right res = Application.Match(WkOr.Value, SourceRng, 0) If WkOr.Comment Is Nothing Then 'do nothing Else 'clear existing comment??? WkOr.Comment.Delete End If If IsError(res) Then 'no match, do nothing Else If SourceRng(res).Comment Is Nothing Then 'nothing to copy Else DestRng.AddComment Text:=SourceRng(res).Comment.Text End If End If 'close the sending workbook SourceRng.Parent.Parent.Close savechanges:=False End Sub Jay wrote: IâââšÂ¬Ã¢âžÂ¢m working on creating a report that will insert comments from a separate workbook. IâââšÂ¬Ã¢âžÂ¢m clear on what I want to achieve, but IâââšÂ¬Ã¢âžÂ¢m sort of clueless on how to get there. My destination worksheet will provide a Work Order number that is the same in the source worksheet. The comments in the source worksheet are in the same cell as the Work Order number itself. Any help would be greatly appreciated. HereâââšÂ¬Ã¢âžÂ¢s what IâââšÂ¬Ã¢âžÂ¢ve got so far (but itâââšÂ¬Ã¢âžÂ¢s not working): Sub Copy_Comment() Dim SourcWbk As Workbook Dim SourcRng1 As Range Dim SourcCmt1 As Range Dim WkOr As Range Dim DestRng As Range Dim cmt As Comment Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls") On Error Resume Next Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200") Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4") Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4") Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_ (WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1) Set cmt = SourcCmt1.Comment DestRng.Value = cmt.Text End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy comments box | Excel Discussion (Misc queries) | |||
Copy Comments to a cell | Excel Programming | |||
Copy Comments | Excel Worksheet Functions | |||
Problem with comments | Excel Discussion (Misc queries) | |||
Comments problem | Excel Discussion (Misc queries) |