Add text to a cell that has a formula

[rpick60]

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?

 

[Ron Coderre]

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]

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"

 

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

 

[rpick60]

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

 

[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

 

[rpick60]

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) &

 

[rpick60]

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

 

[Basilisk96]

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

 

[rpick60]

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.

 

[Basilisk96]

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

 

[rpick60]

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.

 

[rpick60]

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