View Full Version : Solved: How to add comments to a entire column
rafi_07max
11-01-2010, 08:16 AM
I know that I can add comments to a specific cell by right clicking the cell and click insert comments.
But now for e.g. cell A1 contains the word “apple”, I want to remove the word from cell and make it like comment so that when I move mouse cursor onto cell A1 the word ‘apple’ appears.
I want to do this for the entire column.
Sample Pictures
Before:
http://www.iimmgg.com/image/a4a62ae7613e5f43d5156913c8db94b5
After:
http://www.iimmgg.com/image/f0633d713f912c430f454529b4d193f0
I have attached a workbook, where in sheet1 it contains sample file and in sheet2 is what I expecting to get.
4815
craigwg
11-01-2010, 08:22 AM
I would recommend a loop. I'm not great at writing them but there is lots of documentation on the Interwebz. You should be able to loop through a range of cells. I've done this a few ways in the past, but I think that's your answer.
Greetings,
Try:
Option Explicit
Sub exa()
Dim rngData As Range
Dim rngCell As Range
Const FIRST_ROW As Long = 1 '<--- Change to suit
With Sheet1 '<--- Using CodeName, or---> ThisWorkbook.Worksheets("Sheet1")
Set rngData = .Range(.Cells(FIRST_ROW, "A"), .Cells(.Rows.Count, "A").End(xlUp))
For Each rngCell In rngData
With rngCell
If .Comment Is Nothing Then
.AddComment .Value
.ClearContents
End If
End With
Next
End With
End Sub
Hope that helps,
Mark
rafi_07max
11-01-2010, 06:02 PM
Thanks for your help. Your codes work fine. But Your codes only works if the entire column has contents.
For e.g. Cell A1:A5 must be filled in order for the macro to work. If A1 to A3 is filled but A4 is empty cell and then A5-A7 if filled then the macro will have a error. What I mean is that there must not be any empty cell in between for your codes to work.
A sample picture of what i mean
4820
How can I solve this problem?
Kenneth Hobs
11-01-2010, 07:47 PM
Replace the IF code line.
If .Comment Is Nothing And .Value <> Empty Then
rafi_07max
11-01-2010, 08:50 PM
Thank alot Kenneth. It solved the problem.
Do take a look at one of my previous thread where u have helped
http://www.vbaexpress.com/forum/showthread.php?p=228884#post228884
I having some problem in it. Do see whether u could provide any help. Thank You.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.