CODEWITHSUNDEEP
arraysexcelvbacomments
clippertm
clippertm

Reputation: 159

Excel VBA Add Comment from Array

I do not understand why the below does not add comments from A1 to A27 :(

Dim aComment As String

aComment = Split("ABC|DEF|GHI|JKL", "|")

For i = 1 To i = 27
    For x = LBound(aComment) To UBound(aComment)
        With Worksheets("report").Range(Cells(1, i)).AddComment
            .Visible = False
            .Text aComment(x)
        End With
    Next
Next

It would be amazing if you could give me a hand, know I am nearly there, but cannot figure out what I got wrong :(

Upvotes: 1

Views: 1172

Answers (1)

L42
L42

Reputation: 19727

This works if you want to add the comments from A1:A4.

Dim aComment As Variant, i As Long

aComment = Split("ABC|DEF|GHI|JKL", "|")

For i = 1 To 4
    Sheets("report").Range("A" & i).AddComment(aComment(i - 1)).Visible = False
    'Sheets("report").Cells(1, i).).AddComment(aComment(i - 1)).Visible = False
Next

As for the things to improve to make it work:

  1. You need to declare aComment as Variant and not String.
  2. Just use one Loop and a one liner statement.
  3. And you need to fix your For Loop Syntax from For i = 1 To i = 27 to For i = 1 To 27

I only use 4 in the loop since you only gave four samples. You can adjust it to suit.
Also I'm confused. In your question you want to put it in A1:A27 but in comment A1 to D1.
Regardless I provide code for both.

Upvotes: 1

Related Questions