CODEWITHSUNDEEP
vbaexcel
sohal07
sohal07

Reputation: 482

Add comment from adjacent cell value with a condition

I have no experience in VBA. I know there are number of answers to questions similar to this one, but I can't tweak any code to get it working for me.

I have an excel sheet with large number of rows in a table.
There are some values (numbers) in column A and their notes (text) in column B. I want to make those notes (column B) as comments on cells in column A.

But here is the condition:
Some cells in column A already have comments on them and I don't want to replace them with notes. So I need a code which can either skip those specific cells or merge their comments with notes.

Upvotes: 0

Views: 721

Answers (1)

R.Katnaan
R.Katnaan

Reputation: 2526

Here, my approach for your problem:

Public Sub addComment()

    Dim row As Integer
    Dim oldComment As String

    'Set start row
    row = 1

    With Sheets("sheetname")

        'Do until "A" cell is blank
        Do While .Range("A" & row) <> ""

            'If "B" cell is not blank
            If .Range("B" & row) <> "" Then

                'If "A" has no comment, set "" to oldComment
                If .Range("A" & row).Comment Is Nothing Then
                    oldComment = ""

                'Else comment is exist
                Else

                    'Store that comment to oldComment
                    oldComment = .Range("A" & row).Comment.Text & " "

                    'Delete comment from cell
                    .Range("A" & row).Comment.Delete

                End If

                'Insert comment for "A" with old if exist
                .Range("A" & row).addComment (oldComment & .Range("B" & row).Value)

            End If

            'Increase row
            row = row + 1

        Loop

    End With

End Sub

Upvotes: 4

Related Questions