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

E

EXCEL$B!!(BNEWS

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
 
D

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

EXCEL　NEWS

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
 
D

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
 
Top