 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / November 2007Add text to a cell that has a formula
I have a simple question. I have a worksheet that has a list of item to be purchased that is referenced from another sheet. =Sheet1!E10 What i want to do is keep the link but add text after the formula. My end result should be =Sheet1!E10 & " add some kind of note" How can I set up a macro to add text to the formula and allow the user to keep the link to sheet1? You have a couple options: 1) Just add a comment to the cell From the Excel Main Menu: <insert><comment> or 2) Use this technique: =Sheet1!E10+N("enter your comment here") Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) >I have a simple question. I have a worksheet that has a list of item > to be purchased that is referenced from another sheet. =Sheet1!E10 [quoted text clipped - 4 lines] > How can I set up a macro to add text to the formula and allow the user > to keep the link to sheet1? The problem is that the comments to do not carried over to the 3rd and 4th sheet. I am using lists to sort by vender, material and due date. Once I get an updated list I still wan to keep the "note" On Nov 25, 7:38 pm, "Ron Coderre" <ronREMOVETHIScode...@bigfoot.com> wrote: > You have a couple options: > [quoted text clipped - 27 lines] > > - Show quoted text - > I have a simple question. I have a worksheet that has a list of item > to be purchased that is referenced from another sheet. =Sheet1!E10 [quoted text clipped - 4 lines] > How can I set up a macro to add text to the formula and allow the user > to keep the link to sheet1? It looks like you need to simply concatenate the result of the existing formula with some additional text. Try adding this code to a Module: Public Sub AddTextToFormula() txt = InputBox("Enter text to add to formula:") Set rng = Selection rng.Formula = rng.Formula & " & " & Chr(34) & Chr(32) & txt & Chr(34) End Sub Then run AddTextToFormula as a macro. Note that this operates on the current selected range, so be sure to select the cell of interest before running the macro. Cheers, -Basilisk96 > > I have a simple question. I have a worksheet that has a list of item > > to be purchased that is referenced from another sheet. =Sheet1!E10 [quoted text clipped - 22 lines] > Cheers, > -Basilisk96 Thank but hat work on a range of cells, I need it to work on a active cell > > > I have a simple question. I have a worksheet that has a list of item > > > to be purchased that is referenced from another sheet. =Sheet1!E10 [quoted text clipped - 25 lines] > Thank but hat work on a range of cells, I need it to work on a active > cell You're right. Then just use: Public Sub AddTextToFormula() txt = InputBox("Enter text to add to formula:") ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) & Chr(32) & txt & Chr(34) End Sub Cheers, -Basilisk96 > > > > I have a simple question. I have a worksheet that has a list of item > > > > to be purchased that is referenced from another sheet. =Sheet1!E10 [quoted text clipped - 38 lines] > > - Show quoted text - I get an error on this line compile error syntax error ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) & > > > > > I have a simple question. I have a worksheet that has a list of item > > > > > to be purchased that is referenced from another sheet. =Sheet1!E10 [quoted text clipped - 44 lines] > > - Show quoted text - Sorry i copied it wrong. it works great. thanks for the help > > > > > > I have a simple question. I have a worksheet that has a list of item > > > > > > to be purchased that is referenced from another sheet. =Sheet1!E10 [quoted text clipped - 48 lines] > > - Show quoted text - Ok now that works how to I put the message on the next line. (alt + enter) I have added the .wraptext but i want to start the note underneath the link. > Ok now that works how to I put the message on the next line. (alt + > enter) > I have added the .wraptext but i want to start the note underneath the > link. I'd say replace Chr(32) with vbLf, but that shows up as a funky square- shaped character on my system... although it renders the newline correctly when I do F2 on the cell... How does that work for you? Cheers, -Basilisk96 > > Ok now that works how to I put the message on the next line. (alt + > > enter) [quoted text clipped - 8 lines] > Cheers, > -Basilisk96 This is what i got to work txt = InputBox("Enter text to add to formula:") ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) & Chr(32) & Chr(10) & txt & Chr(34) ActiveCell.WrapText = True Thanks again for the help > > > > > > > I have a simple question. I have a worksheet that has a list of item > > > > > > > to be purchased that is referenced from another sheet. =Sheet1!E10 [quoted text clipped - 55 lines] > > - Show quoted text - I got it chr(10) before the txt field. Thanks a lot for your help. > > > > > I have a simple question. I have a worksheet that has a list of item > > > > > to be purchased that is referenced from another sheet. =Sheet1!E10 [quoted text clipped - 42 lines] > > ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) & So do I :)) ...unless I tack on the rest of the statement from the line that follows: Chr(32) & txt & Chr(34) The posts unfortunately break up lines into shorter ones, hence your error. But just to be 100% safe (with my tongue in cheek), you can use this version: ActiveCell.Formula = _ ActiveCell.Formula & _ " & " & _ Chr(34) & _ Chr(32) & _ txt & _ Chr(34) Cheers, -Basilisk96 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.