Add cell comments from one cell to another that already has commante in it

[andyba]

I have to combine the cell contents (values and cell comments) from one cell, to another cell (on the same row) that already contains a value and a comment. I can use paste special to add the values together (I don't need to retain the individual values) but I need to show both sets of comments in the resulting cell.

Eg Cell C2 has a value of 25.00 and a cell comment of CPA 1/5/11.
Cell D2 has a value of 75.00 and a cell comment of CPA 25/5/11.

Cell C2 should now show a value of 100.00 and a cell comment of CPA 1/5/11 CPA 25/5/11
This has to be carried out on several thousand rows of data.

Any help would be greatly appreciated.

 

[JoeMo]

Try this (untested) on a copy of your worksheet. Assumes your data start at C2 and Column D counterparts to C cells are what you want to add to the C cells.

Sub JoinValuesAndComments()
Dim cR As Range, dR As Range
Dim lrw As Long
lrw = Range("C" & Rows.Count).End(xlUp).Row
Set cR = ActiveSheet.Range("c2", "C" & lrw)
Set dR = cR.Offset(0, 1)
For i = 1 To cR.Rows.Count
    cR.Cells(i, 1).Value = cR.Cells(i, 1).Value + dR.Cells(i, 1).Value
    On Error Resume Next
    cR.Cells(i, 1).Comment.Text dR.Cells(i, 1).Comment.Text, Len(cR.Cells(i, 1).Comment.Text) + 1, False
    If Err.Number <> 0 Then
        cR.Cells(i, 1).AddComment dR.Cells(i, 1).Comment.Text
        On Error GoTo 0
    End If
Next i
End Sub

 

[andyba]

Thanks for such a quick response! That works fine!