Converting cell content into comment
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 |
Converting cell content into comment
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 |
Converting cell content into comment
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 |
Converting cell content into comment
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 |
Converting cell content into comment
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 |
Converting cell content into comment
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 |
Converting cell content into comment
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. |
Converting cell content into comment
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 |
Converting cell content into comment
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 |
Converting cell content into comment
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 |
All times are GMT +1. The time now is 01:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com