CODEWITHSUNDEEP
excelvba
mbur_1
mbur_1

Reputation: 5

Add a comment to every cell in a row with For Each loop (VBA Excel)

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

Upvotes: 0

Views: 538

Answers (1)

Warcupine
Warcupine

Reputation: 4640

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

Upvotes: 2

Related Questions