Add 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?

Add text to a cell that has a formula
 

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)

"rpick60" wrote in message
...
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?

Add text to a cell that has a formula
 

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

"rpick60" wrote in message

...



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?- Hide quoted text -

- Show quoted text -

Add text to a cell that has a formula
 

On Nov 25, 7:25 pm, rpick60 wrote:
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?

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

Add text to a cell that has a formula
 

On Nov 25, 7:52 pm, Basilisk96 wrote:
On Nov 25, 7:25 pm, rpick60 wrote:

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?

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

Thank but hat work on a range of cells, I need it to work on a active
cell

Add text to a cell that has a formula
 

On Nov 25, 8:00 pm, rpick60 wrote:
On Nov 25, 7:52 pm, Basilisk96 wrote:



On Nov 25, 7:25 pm, rpick60 wrote:

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?

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

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

Add text to a cell that has a formula
 

On Nov 25, 8:03 pm, Basilisk96 wrote:
On Nov 25, 8:00 pm, rpick60 wrote:





On Nov 25, 7:52 pm, Basilisk96 wrote:

On Nov 25, 7:25 pm, rpick60 wrote:

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?

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

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- Hide quoted text -

- Show quoted text -

I get an error on this line compile error syntax error

ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) &

Add text to a cell that has a formula
 

On Nov 25, 8:09 pm, rpick60 wrote:
On Nov 25, 8:03 pm, Basilisk96 wrote:





On Nov 25, 8:00 pm, rpick60 wrote:

On Nov 25, 7:52 pm, Basilisk96 wrote:

On Nov 25, 7:25 pm, rpick60 wrote:

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?

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

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- Hide quoted text -

- Show quoted text -

I get an error on this line compile error syntax error

ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) &- Hide quoted text -

- Show quoted text -

Sorry i copied it wrong. it works great. thanks for the help

Add text to a cell that has a formula
 

On Nov 25, 8:09 pm, rpick60 wrote:
On Nov 25, 8:03 pm, Basilisk96 wrote:



On Nov 25, 8:00 pm, rpick60 wrote:

On Nov 25, 7:52 pm, Basilisk96 wrote:

On Nov 25, 7:25 pm, rpick60 wrote:

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?

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

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- Hide quoted text -

- Show quoted text -

I get an error on this line compile error syntax error

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

Add text to a cell that has a formula
 

On Nov 25, 8:12 pm, rpick60 wrote:
On Nov 25, 8:09 pm, rpick60 wrote:





On Nov 25, 8:03 pm, Basilisk96 wrote:

On Nov 25, 8:00 pm, rpick60 wrote:

On Nov 25, 7:52 pm, Basilisk96 wrote:

On Nov 25, 7:25 pm, rpick60 wrote:

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?

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

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- Hide quoted text -

- Show quoted text -

I get an error on this line compile error syntax error

ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) &- Hide quoted text -

- Show quoted text -

Sorry i copied it wrong. it works great. thanks for the help- Hide quoted text -

- 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.

Add text to a cell that has a formula
 

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

Add text to a cell that has a formula
 

On Nov 25, 8:20 pm, rpick60 wrote:
On Nov 25, 8:12 pm, rpick60 wrote:





On Nov 25, 8:09 pm, rpick60 wrote:

On Nov 25, 8:03 pm, Basilisk96 wrote:

On Nov 25, 8:00 pm, rpick60 wrote:

On Nov 25, 7:52 pm, Basilisk96 wrote:

On Nov 25, 7:25 pm, rpick60 wrote:

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?

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

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- Hide quoted text -

- Show quoted text -

I get an error on this line compile error syntax error

ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) &- Hide quoted text -

- Show quoted text -

Sorry i copied it wrong. it works great. thanks for the help- Hide quoted text -

- 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.- Hide quoted text -

- Show quoted text -

I got it chr(10) before the txt field.
Thanks a lot for your help.

Add text to a cell that has a formula
 

On Nov 25, 8:35 pm, Basilisk96 wrote:
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

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