I think you're right that this method probably isn't ideal for my
requirements, but it will be a learning experience to try it out.
Thank you
Yvette
"RichardSchollar" wrote:
I don't want to be pedantic, but VBA isn't your only option (altho I
think it's by far the best and most flexible and would the be route I
would choose if I needed to do this) you can also use a relatively
inflexible XLM4 macro function:
InsertNameDefine and in the Refers To box type:
=EVALUATE(A$1&A$2&A$3)
and give it a name like "FormulaResult"
Now, back in the worksheet, select A6 and type
=FormulaResult
which should return 8 as the result of 4+4.
This is jolly inflexible however (It only evaluates 3 cells for
example), but it is an alternative.
Richard
Chip Pearson wrote:
You can only do it with VBA. Put the following code in a standard Code
Module in VBA.
Function EvalConcatCells(DataCells As Range) As Variant
Dim R As Range
Dim V As Variant
Dim S As String
For Each R In DataCells.Cells
S = S & R.Text & " "
Next R
On Error Resume Next
V = Evaluate(S)
If Err.Number = 0 Then
EvalConcatCells = V
Else
EvalConcatCells = CVErr(xlErrValue)
End If
End Function
You could then call this with a worksheet function like
=EvalConcatCells(A1:A4)
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"Yvette" wrote in message
...
Hi,
I hope I've selected the right discussion group. I want to concatenate
the
values of a set of cells as a string, and then have Excel recognise the
string as a formula. For example:
A1: 4
A2: +
A3: 4
A5: =
A6: =A1&A2&A3
I want cell A6 to return the answer to 4 + 4, and not the string "4+4".
Any ideas? (I'm using Excel 2003)
Thanks
Yvette