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

On Sep 4, 9:41 am, Akash wrote:
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

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" wrote:

On Sep 4, 9:41 am, Akash wrote:
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

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

On Sep 4, 11:34 am, Zoltan wrote:
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" wrote:
On Sep 4, 9:41 am, Akash wrote:
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

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" wrote:

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

On Sep 4, 11:34 am, Zoltan wrote:
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" wrote:
On Sep 4, 9:41 am, Akash wrote:
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

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

On Sep 4, 12:28 pm, Zoltan wrote:
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" wrote:
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

On Sep 4, 11:34 am, Zoltan wrote:
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" wrote:
On Sep 4, 9:41 am, Akash wrote:
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

On Tue, 04 Sep 2007 10:13:01 -0000, Akash wrote:

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
---
It works (tried), and it take a bit less time since it does not loop
through every cell in that worksheet.

B.

On Sep 4, 4:00 pm, Boris wrote:
On Tue, 04 Sep 2007 10:13:01 -0000, Akash wrote:
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
---
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

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" wrote:

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

On Sep 4, 12:28 pm, Zoltan wrote:
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" wrote:
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

On Sep 4, 11:34 am, Zoltan wrote:
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" wrote:
On Sep 4, 9:41 am, Akash wrote:
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

On Sep 4, 4:26 pm, Zoltan wrote:
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" wrote:
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

On Sep 4, 12:28 pm, Zoltan wrote:
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" wrote:
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

On Sep 4, 11:34 am, Zoltan wrote:
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" wrote:
On Sep 4, 9:41 am, Akash wrote:
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

ya i did that... thanks a tooonnnn