Converting cell content into comment

[Akash]

Hi,

I have a sheet in which Leave is mentioned many times in different
cell.

I want to have a macro through which i can replace the word Leave and
put a Comment with Leave in the cell

How can i do that.

thanks in advance

Akash

 

[Akash]

Hi,

I have a sheet in which Leave is mentioned many times in different
cell.

I want to have a macro through which i can replace the word Leave and
put a Comment with Leave in the cell

How can i do that.

thanks in advance

Akash

I have a sheet in which Leave is mentioned many times in different
cell. I want to have a macro through which i can replace the word
Leave and put a Comment with Leave in that comment to a particular
cell. How can i do that.

thanks in advance

Akash

 

[Guest]

Akash,

try this code:

Private Sub CommandButton2_Click()

dim rng as range
dim mycell as range

set rng = range("A1:C5") ' the range you would like to use

for each mycell in rng
if mycell.find ("Leave") then
mycell.Replace what:="Leave", replacement:="x" 'x is the replacement
string
mycell.addcomment "Leave"
end if
Next

End Sub

I hope it helps,
Zoltan

 

[Akash]

Hi Zoltan,

Thanks for the help but its not working. I am facing error in the
line:

If mycell.Find("Leave") Then

Pls do tell me what should i do in this regard.

Thanks

Akash

 

[Guest]

Sorry,
my mistake. Please see the code which works well (this time I tested)


Private Sub CommandButton1_Click()
Dim rng As Range
Dim mycell As Range

Set rng = Range("A1:C5") ' the range you would like to use

For Each mycell In rng
If Not mycell.Find("Leave") Is Nothing Then
mycell.Replace what:="Leave", replacement:="x" 'x is the replacement
string
mycell.AddComment "Leave"
Else
mycell.ClearComments
End If
Next


End Sub



Regards,
Zoltan

 

[Akash]

Hi still its not working dear.

do i have to add a Button to my sheet.

I dont want the button to be added in my sheet. I want a normar code
for the same.

Pls do help.

Thanks

Akash

 

[Boris]

Hi still its not working dear.

....

Akash,

I've already posted this as answer tou you in
microsoft.public.excel.programming group, but here it is one more time:
---
Sub DoComment()
Dim R As Range, rF As Range, cFirst As String

Set R = ActiveSheet.UsedRange.Find("Leave", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not R Is Nothing Then
cFirst = R.Address
Do
R.AddComment "Leave in cell"
Set R = ActiveSheet.UsedRange.FindNext(R)
Loop Until R Is Nothing Or R.Address = cFirst
End If
End Sub

 

[Akash]

...

Akash,

I've already posted this as answer tou you in
microsoft.public.excel.programming group, but here it is one more time:
---
Sub DoComment()
Dim R As Range, rF As Range, cFirst As String

Set R = ActiveSheet.UsedRange.Find("Leave", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not R Is Nothing Then
cFirst = R.Address
Do
R.AddComment "Leave in cell"
Set R = ActiveSheet.UsedRange.FindNext(R)
Loop Until R Is Nothing Or R.Address = cFirst
End If
End Sub
---
It works (tried), and it take a bit less time since it does not loop
through every cell in that worksheet.

B.

Thanks a tonnn for the solution . Thank u very much

 

[Guest]

Akash,
it works. I tested it. You need to add a button, and you need to copy this
code to the onclick event of the button.
Otherwise, you need something to execute your commands. And a button is an
easy way to tell Excell when and what to do.
Do you have any plan how to replace the button?Please tell me and hopfully I
can help.

Zoltan

 

[Akash]

Akash,
it works. I tested it. You need to add a button, and you need to copy this
code to the onclick event of the button.
Otherwise, you need something to execute your commands. And a button is an
easy way to tell Excell when and what to do.
Do you have any plan how to replace the button?Please tell me and hopfully I
can help.

Zoltan

ya i did that... thanks a tooonnnn