Show Formula Contest Automatically 4

[iv63]

Variables a and b are defined in cells B1 & B2. Cell B3 has a formula 2a+b/3 (=2*B1+B2/3). I want in cell C3 contest of this formula (2*1+2/3) not (=2*B1+B2/3). This can be done manually like this ="(2*"&TEXT(B1,0)&"+"&TEXT(B2,0)&"/3)"
I would appreciate if you already have Macro or VBA that does this automatically and you do not mind sharing it.
Regards,
iv

 

[IDS]

Why go into VBA to the extent as shown above. The simple formulae of ="2 x "&B1&" + "&B2&" / 3" works a treat, is very simple and easy to understand

Because:

1: Typing in the formula for each cell where it is required takes much longer than selecting a range and typing Alt-F8

2: If the purpose is auditing formulae already entered in the spreadsheet, it's a bit pointless using a procedure that requires manually entering the formulae again.

3: Copying and pasting the VBA provided is a simple operation that should be within the capabilities of anyone who uses Excel for engineering purposes.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

Blog post and sample spreadsheet for download here:

http://newtonexcelbach.wordpress.com/2011/04/28/showing-numerical-values-of-a-formula/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[iv63]

Doug:
Big star for you!!! My coworker told me that her previous company has the Function. I will keep “searching” … Thank you all for your valuable contributions.
iv

 

[IDS]

iv63 - if you do find a function that does this I'd be very interested to see it.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[GrahamG]

A small modification makes the result dynamic and units can be added.

Sub ConvertFormulaToText()
Dim intRow As Integer
Dim intCol As Integer
Dim intCountRow As Integer
Dim strFormula As String
Dim myRange As Range
Dim strVal As String
Dim strText As String
Dim strAdd As String
Dim strFormat As String

If Selection.Columns.Count > 1 Then
MsgBox "Too many columns selected"
Exit Sub
End If

intCol = Selection.Column
intRow = Selection.Row
strFormat = "0.00"

For intCountRow = 1 To Selection.Rows.Count
Cells(intRow + intCountRow - 1, intCol).Select
strFormula = ActiveCell.Formula
strAdd = ActiveCell.Address(False, False)

If Len(strFormula) = 0 Then GoTo MissRow

For Each myRange In ActiveCell.Precedents.Cells
With myRange
strVal = " " & Range(.Address).Address & " "
strText = Chr(34) & " & Text(" & strVal & "," & Chr(34) & strFormat & Chr(34) & ") &" & Chr(34)
strFormula = Replace(strFormula, .Address, strText)
strFormula = Replace(strFormula, .Address(RowAbsolute:=False), strText)
strFormula = Replace(strFormula, .Address(ColumnAbsolute:=False), strText)
strFormula = Replace(strFormula, .Address(RowAbsolute:=False, ColumnAbsolute:=False), strText)
End With
Next

strFormula = Chr(34) & strFormula & Chr(34)

'Add the result
strFormula = strFormula & " & " & Chr(34) & "=" & Chr(34) & " & " & "TEXT(" & strAdd & "," & Chr(34) & strFormat & Chr(34) & ")"

'Add units
If Len(ActiveCell.Offset(0, 1).Value) Then strFormula = strFormula & " & " & Chr(34) & " " & ActiveCell.Offset(0, 1).Value & Chr(34)

'Add some spaces to improve readability
strFormula = Replace(strFormula, "=", " = ")
strFormula = Replace(strFormula, "+", " + ")
strFormula = Replace(strFormula, "-", " - ")

'Add =
ActiveCell.Offset(0, 2).Formula = "=" & strFormula

MissRow:

Next intCountRow

End Sub

 

[iken]

IDS

How does your method become easier than selecting range (as you also need to do), and hitting crtl D?

For formulae checking, you need to check all the lines in your routine, where you only need to check the first formulae. And now in excel 2010, you get a little icon showing if your formulaeas are inconsistant - very handy.

I still think on alot of items, people seem to think VBA is the way to go, when excel is more than cabable to achieve what is requried in the "front view" of excel (without stepping in the behind the scenes VBA).

I was also told some years back, that doing a simple excerise in VBA, will be alot slower (tie up your computer) than if you use excels built in functions.

 

[electricpete]

iken - the original question did not ask for a strategy to design a spreadsheet... it asked for a way to display formula's with values inserted.

In some cases it is required not only to be easily created, but easily checked by a 3rd party (well documented).

Personally, I don't see a lot of value in inserting a number into the forumula... I prefer to use named ranges as mentioned 8 Dec 10 14:56 so the formula ends up looking like a formula you read in a textbook... with names.... easy to verify imo by looking at the formula which includes names. But each person and task has it's own demans and knowing how to accomplish things different ways is helpful.


=====================================
(2B)+(2B)' ?

 

[IRstuff]

This particular type of problem seems to be unique to structural engineering, and it appears that someone other than the creator of the calculation demands that equations be displayed with all values substituted, probably because it simplifies the hand checking.

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[IDS]

iken - you are right, if the only formula you ever use is (=2*B1+B2/3) then it is easier to use your string formula and copy it down.

Possibly iv63 sometines uses other formulas though, and only gave that as an example.

In general, I see here people going to great lengths to avoid VBA far more often than usining VBA unnecessarily. Also if speed is an issue a well written VBA routine can often be quicker than using only spreadsheet formulas.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/