Is it possible to sequentially number the comments field

[NickTheBatMan]

Anyone out there clever enough to tell me how to sequentially populate
the comments field in cells... the problem I can see is that the cells
are in a grid of A5-10 through to i5 to 10, I want them populating
a5,a6,a7,a8,a9,a10,b5,b6 etc and when I get to i10 I want the sequence
to carry over to the next sheet...

Don't want much do I

TIA

Nick

 

[Rick Rothstein \(MVP - VB\)]

It looks like rather than "sequentially" populating the Comment fields, you
are simply putting the cell's address into the Comment field. If that
observation is correct, then I do not know what you mean when you said "when
I get to i10 I want the sequence to carry over to the next sheet"? Do you
simply want to put the cell address into each cell with the Range("A5:I10")
on each sheet? If so, consider this macro...

Sub PutCellAddressIntoComment()
Dim C As Range
Dim WS As Worksheet
Dim CurrentText As String
For Each WS In Worksheets
For Each C In WS.Range("A1:I10")
If Not C.Comment Is Nothing Then
' If you do NOT want to preserve any existing
' Comment text then remove this next statement
CurrentText = " - " & C.Comment.Text
C.Comment.Delete
End If
C.AddComment LCase(C.Address(False, False)) & CurrentText
Next
Next
End Sub

Rick