Help with adding comment from scripting dictionary item

tomleitch

Board Regular
Joined
Jan 3, 2012
Messages
188
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


Code:
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?

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




Thanks
Tom
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,548
Messages
5,511,989
Members
408,873
Latest member
Lorid24

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top