how to make a formula to be "&"s formula

[=?iso-2022-jp?B?RVhDRUwbJEIhIRsoQk5FV1M=?=]

hi,
could anybody tell me something about the way to make a formula to be "&"s
formula.
i mean that, for instance ,make =D22-G28-D26 to be =D22&" $B!](B "&G28&" $B!](B
"&D26,

for i like the show the detail of the formula,instead of the result.

thanks

best regards

 

[mg]

Dnia Wed, 17 Nov 2004 21:47:43 +0900, EXCEL　NEWS napisał(a):

hi,
could anybody tell me something about the way to make a formula to be "&"s
formula.
i mean that, for instance ,make =D22-G28-D26 to be =D22&" − "&G28&" −
"&D26,

for i like the show the detail of the formula,instead of the result.

thanks

best regards

if you use English version of Xl then :



Function FormulaToString(Cel As Range) As String

If Cel.Areas.Count = 1 Then
If Cel.HasFormula Then
FormulaToString = Cel.Formula
End If
End If

End Function

 

[mg]

Dnia Wed, 17 Nov 2004 14:18:27 +0100, mg napisał(a):

Dnia Wed, 17 Nov 2004 21:47:43 +0900, EXCEL　NEWS napisał(a):


if you use English version of Xl then :

Function FormulaToString(Cel As Range) As String

If Cel.Areas.Count = 1 Then
If Cel.HasFormula Then
'FormulaToString = Cel.Formula
FormulaToString = Cel.FormulaLocal
End If
End If
End Function

 

[Dave Peterson]

I think you meant this:

If Cel.Cells.Count = 1 Then
instead of:
If Cel.Areas.Count = 1 Then

A1:x99 is a range with just one area.

 

[Guest]

Thanks for your advice.

but　i　mean　that　 =D22&" − "&G28&" −"&D26　is　the　formula　connect　
with　"&"
in　order to show the value instead of the cell address,
as a matter of fact ,for example ,1.23-2.36-36.23 will be seen in the cell.
thanks

 

[Dana DeLouis]

Hello. If your formulas doesn't reference anything outside the current
sheet, maybe this might help. It's not very sophisticated, but maybe it
will give you some ideas.

Set rng = Range("C1") 'Range with formulas
Range("D1") = Convert(rng) ' Addresses converted to values

For testing, C1 had the formula
=A1-A2+A5-A6
and D1 ended up with
=10-11+12-13

It should adjust formulas like
=SQRT(A1)
to
=SQRT(25)

Function Convert(rng) As String
Dim j As Long
Dim k As Long
Dim s As String
Dim v As Variant

s = rng.Formula
With rng.Precedents
For j = 1 To .Areas.Count
Set v = Range(.Areas(j).Address)
For k = 1 To v.Cells.Count
s = Replace(s, v(k).Address(True, True), v(k))
s = Replace(s, v(k).Address(True, False), v(k))
s = Replace(s, v(k).Address(False, True), v(k))
s = Replace(s, v(k).Address(False, False), v(k))
Next k
Next j
End With
Convert = s
End Function


Again, not tested very well and it doesn't have error checking.
HTH

 

[mg]

Dnia Thu, 18 Nov 2004 21:22:46 +0900, EXCEL　NEWS napisal(a):

Thanks for your advice.

but　i　mean　that　 =D22&" − "&G28&" −"&D26　is　the　formula　connect　
with　"&"
in　order to show the value instead of the cell address,
as a matter of fact ,for example ,1.23-2.36-36.23 will be seen in the cell.
thanks

I'm not sure if i understand you correctly . Try following function .
It works only with rows but you can easily modify it to work with any range
delim is sign or string to separate values


Function RowToString(RowRange As Range, delim As String) As String
Dim I As Integer
If RowRange.Cells.Count > 0 Then
ReDim Tabl(1 To RowRange.Columns.Count)
For I = 1 To RowRange.Columns.Count - 1
RowToString = RowToString & CStr(RowRange.Cells(1, I).Value) & delim
Next I
RowToString = RowToString & CStr(RowRange.Cells(1, I).Value)
Else
RowToString = ""
End If

End Function

 

[JulieD]

Hi mg

just type a
'
in front of the formula
e.g.
'=D22-G28-D26

Cheers
JulieD