ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copy Comments problem (https://www.excelbanter.com/excel-programming/383986-copy-comments-problem.html)

Jay
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

Gary''s Student
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

Dave Peterson
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

Dave Peterson
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

Jay
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

Jay
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:

彜o 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 廝 of work order numbers and populate the corresponding
column 廛 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

Jay
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

Dave Peterson
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:

彜o 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 廝 of work order numbers and populate the corresponding
column 廛 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

Jay
Copy Comments problem
 
That worked beautifully! Once again, thank you so much.

Actually I did notice the 徇istake in the original code and removed that IF
statement. I actually do not intend on 徇oving 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 弩ork Orders or sheet 廚ompleted.

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:

ﾃ｢ぎﾅ鉄o 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 ﾃ｢ぎﾅ釘ﾃ｢ぎﾂ of work order numbers and populate the corresponding
column ﾃ｢ぎﾅ泥ﾃ｢ぎﾂ 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
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 徇istake in the original code and removed that IF
statement. I actually do not intend on 徇oving 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 弩ork Orders or sheet 廚ompleted.

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:

ﾃ｢ぎﾅ鉄o 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 ﾃ｢ぎﾅ釘ﾃ｢ぎﾂ of work order numbers and populate the corresponding
column ﾃ｢ぎﾅ泥ﾃ｢ぎﾂ 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

Jay
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 ﾃ｢ぎﾅ杜istakeﾃ｢ぎﾂ in the original code and removed that IF
statement. I actually do not intend on ﾃ｢ぎﾅ杜ovingﾃ｢ぎﾂ 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 ﾃ｢ぎﾅ展ork Ordersﾃ｢ぎﾂ or sheet ﾃ｢ぎﾅ鼎ompletedﾃ｢ぎﾂ.

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:

ﾃδ｢ﾃ｢堋ｬﾃ彜o 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 ﾃδ｢ﾃ｢堋ｬﾃ廝ﾃδ｢ﾃ｢堋ｬﾃつ of work order numbers and populate the corresponding
column ﾃδ｢ﾃ｢堋ｬﾃ廛ﾃδ｢ﾃ｢堋ｬﾃつ 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
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 ﾃ｢ぎﾅ杜istakeﾃ｢ぎﾂ in the original code and removed that IF
statement. I actually do not intend on ﾃ｢ぎﾅ杜ovingﾃ｢ぎﾂ 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 ﾃ｢ぎﾅ展ork Ordersﾃ｢ぎﾂ or sheet ﾃ｢ぎﾅ鼎ompletedﾃ｢ぎﾂ.

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:

ﾃδ｢ﾃ｢堋ｬﾃ彜o 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 ﾃδ｢ﾃ｢堋ｬﾃ廝ﾃδ｢ﾃ｢堋ｬﾃつ of work order numbers and populate the corresponding
column ﾃδ｢ﾃ｢堋ｬﾃ廛ﾃδ｢ﾃ｢堋ｬﾃつ 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


All times are GMT +1. The time now is 03:31 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com