dakkat
05-04-2010, 11:30 AM
Hello,
I have been struggling with this section of code. I am populating values to a spreadsheet based upon raw data on another sheet. I get the field data to appear but am unable to add a value to the comments section of the same field. The code runs and adds the comment triangle in the first cell but does not copy any data and gives the following error:
Run-time error '1004':
Copy Method of Worksheet Class failed
The code for this particular function is below:
Dim LR As Long, i As Long, X As Variant
With Sheets("rd")
LR = .Range("N" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("N" & i)
X = Application.Match(.Value, Sheets("overview").Range("B1:B500"), 0)
If IsNumeric(X) Then .Offset(, 22).Copy Destination:=Sheets("overview").Cells(X, Columns.Count).End(xlToLeft).Offset(, 1)
If IsNumeric(X) Then .Offset(, -12).Copy Destination:=Sheets("overview").Cells(X + 1, Columns.Count).End(xlToLeft).Offset(, 1)
If IsNumeric(X) Then .Offset(, -11).Copy Destination:=Sheets("overview").Cells(X + 1, Columns.Count).End(xlToLeft).Offset(0, 0).AddComment.Text
End With
Next i
End With
First one copies the score value to the correct cell. Second copy adds the ID code to the cell directly below the score. Third (one having issues) is suppose to add its value into the comment of the second (ID code) cell.
If I remove the 'AddComment.Text' from the code, it will copy the value to the cell. I need to copy the value into the comment of the cell.
Can anyone shed some light here? Any help would be greatly appreciated.
I have been struggling with this section of code. I am populating values to a spreadsheet based upon raw data on another sheet. I get the field data to appear but am unable to add a value to the comments section of the same field. The code runs and adds the comment triangle in the first cell but does not copy any data and gives the following error:
Run-time error '1004':
Copy Method of Worksheet Class failed
The code for this particular function is below:
Dim LR As Long, i As Long, X As Variant
With Sheets("rd")
LR = .Range("N" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("N" & i)
X = Application.Match(.Value, Sheets("overview").Range("B1:B500"), 0)
If IsNumeric(X) Then .Offset(, 22).Copy Destination:=Sheets("overview").Cells(X, Columns.Count).End(xlToLeft).Offset(, 1)
If IsNumeric(X) Then .Offset(, -12).Copy Destination:=Sheets("overview").Cells(X + 1, Columns.Count).End(xlToLeft).Offset(, 1)
If IsNumeric(X) Then .Offset(, -11).Copy Destination:=Sheets("overview").Cells(X + 1, Columns.Count).End(xlToLeft).Offset(0, 0).AddComment.Text
End With
Next i
End With
First one copies the score value to the correct cell. Second copy adds the ID code to the cell directly below the score. Third (one having issues) is suppose to add its value into the comment of the second (ID code) cell.
If I remove the 'AddComment.Text' from the code, it will copy the value to the cell. I need to copy the value into the comment of the cell.
Can anyone shed some light here? Any help would be greatly appreciated.