Maybe...

Option Explicit
Sub testme()

Dim myCell As Range
Dim myStr As String

Set myCell = Worksheets("defaults").Range("c5")

myStr = "=""test ""&" & myCell.Address(external:=True) & "&"" test"""

ActiveCell.Formula = myStr

'or

ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Test " & myCell.Text & " Test"

End Sub

But remember, if you use .formulaR1C1, you can't refer to a cell as C5 (r5c3 is
fine).

In R1C1 notation, C5 means Column 5 (E).



maperalia wrote:

Jim;
Your code for wonderful in the cell. However, I have tried to do apply it in
VBA and the formula did not work.
What I want to type is:
'ActiveCell.FormulaR1C1 = "test &=DEFAULTS!C5 & test"

Could you please help me?

Thanks in advance.
Maperalia

"Jim Thomlinson" wrote:

Try this. Your posted formula did not need the second sum function or the ":"
... Otherwise it should work just fine.

=SUM(J5:J20) & " Text here " & (J20+K20+L20)/3
or
=SUM(J5:J20) & " Text here " & sum(J20:L20)/3

--
HTH...

Jim Thomlinson


"HotRod" wrote:

I have a formula that SUMS the rows above it but also want to show the
overall percent. Can I do a SUM and text and another SUM in one cell without
additional hidden cells?

Something like this?

=SUM(J5:J20) & "Text here" & SUM((J:20+K:20+L:20)/3)




--

Dave Peterson