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 <maheshwari.ak...@gmail.com> 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 <maheshwari.ak...@gmail.com> 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 <Zol...@discussions.microsoft.com> 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 <maheshwari.ak...@gmail.com> 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 <Zol...@discussions.microsoft.com> 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 <maheshwari.ak...@gmail.com> 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 <Zol...@discussions.microsoft.com> 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 <Zol...@discussions.microsoft.com> 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 <maheshwari.ak...@gmail.com> 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 <boris_@_lanx.hr> 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 <Zol...@discussions.microsoft.com> 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 <Zol...@discussions.microsoft.com> 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 <maheshwari.ak...@gmail.com> 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 <Zol...@discussions.microsoft.com> 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 <Zol...@discussions.microsoft.com> 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 <Zol...@discussions.microsoft.com> 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 <maheshwari.ak...@gmail.com> 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