0

I'm trying to add a comment to each cell of the second column in an excel sheet with a For Each Loop. I would like to write the cell address (e.g. $B$1) into the comment. This is my code so far, but it doesn't work. I can't figure out what goes wrong. Sorry, I'm a noob and don't know how to get the references to the cells in the second line of the loop to work:

Sub TestZelladresse()
    
    Dim rng As Range
    Dim cell As Range
    
    Set rng = Columns(2)
    
    For Each cell In rng
    .AddComment (Address(cell))
    Next cell
    
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
mbur_1
  • 3
  • 3
  • 5
    `cell.Address`? Note that `Set rng = Columns(2)` means the entire column all the way to the last row in the sheet, not the last row with data). – BigBen Sep 29 '21 at 14:58

1 Answers1

2

As BigBen pointed out Address is a property of a range object, not a function that you pass a range to.

You cannot use a For Each with a Columns Range object, since it will loop through the columns and not the cells within the column (once again thanks BigBen), and you can't add a comment to a range in one shot like that. Additionally, also pointed out by BigBen, you don't want to loop through an entire column, that's a lot of cells.

Lastly you need the object you're calling a method / property from to be included in the line, unless you use a With which is just some syntactic sugar for the same thing.

So all together something like this:

Sub TestZelladresse()
    Dim rng As Range
    Dim cell As Range
    Set rng = Range(Cells(1, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2)) 

    For Each cell In rng
        cell.AddComment cell.Address 
    Next cell
    
End Sub
Warcupine
  • 4,460
  • 3
  • 15
  • 24