Help with adding comment from scripting dictionary item


Board Regular
Jan 3, 2012
I'm using this code, although I've amended it a bit and one of the things that I want to do is to get it to add a comment in to a cell

With CreateObject("scripting.dictionary") 

      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
      If Not .exists(Cl.Value) Then .Add Cl.Value, Array(Cl.Offset(, 1).Value, Cl.Offset(, 2).Value, Cl.Offset(, 3).Value, Cl.Offset(, 4).Value, Cl.Offset(, 5).Value, Cl.Offset(, 6).Value, Cl.Offset(, 7).Value, Cl.Offset(, 8).Value)
      Next Cl
    ' Remove cl if existing in archived

       For Each Cl In Ws3.Range("A2", Ws3.Range("A" & Rows.Count).End(xlUp))
       If .exists(Cl.Value) Then .Remove (Cl.Value)
       Next Cl

      For Each Cl In Ws1.Range("A10", Ws1.Range("A" & Rows.Count).End(xlUp))
            If .exists(Cl.Value) Then
            'Check/update Desc and add location as comment
            If Trim(UCase(Cl.Offset(, 1).Value)) <> Trim(UCase(.Item(Cl.Value)(0))) Then
            Cl.Offset(, 1).Value = .Item(Cl.Value)(0)
            On Error Resume Next
            Cl.Offset(, 1).ClearComments
            On Error GoTo 90
            Cl.Offset(, 1).AddComment
            Cl.Offset(, 1).Comment.Text = .Item(Cl.Value)(6)
            Cl.Offset(, 1).Interior.Color = rgbYellow
            End If
So that's a part of the code and the bit I'm having trouble with is the bit where I'm trying to add comment text.

Can anyone help?

Cl.Offset(, 1).Comment.Text = .Item(Cl.Value)(6)
is the bit that seems to be wrong.


Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...