Excel
Excel
(OP)
Is there a way where you could display both the formula and its out put in the same cell.
For instance:
I am using a "substitution" formula which removes dashes from numbers- I want the output of that formula to be displayed in the same cell.
OR:
If there is a way excel can automatically format a cell when i paste something into it. Please help..
Regards
For instance:
I am using a "substitution" formula which removes dashes from numbers- I want the output of that formula to be displayed in the same cell.
OR:
If there is a way excel can automatically format a cell when i paste something into it. Please help..
Regards





RE: Excel
RE: Excel
If you have another cell to spare, it would be easy.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel
That said, you can do this:
> Create a new window on the same sheet
> Tile them together in the same window
> Pick one view and select Tools | Options | View | check Formulas
You should get one window showing formulas, and the other showing results.
Your other choice is to copy the contents of any given cell and paste into another cell as text. Less "live" and prone to de-synchronization of the formulas and their "display"
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Excel
="A1 + B1 = "&(A1+B1)
will produce:- A1 + B1 = 3
not ideal but you can expand this example to meet most formulae. The problem with this method is that it can be difficult to use the answer produced in further calculations (though not impossible) and amend the formulae to read correctly after amendments.
cherrypicker
RE: Excel
If as CherryPicker interpretted you want to display a formula and it's result... displayed in a different cell than the location where the data is stored.... there is a more general way to do it. VBA function as follows:
Function DisplayFormulaAndResult(Cell As Range)
GetFormula = CStr(Cell.Formula + " = " + CStr(Cell.Value))
End Function
Note this is just a small adaptation of the "getformula" function which can be found by searching this forum.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel
CODE
MYDISPLAY = CStr(Cell.Formula + " = " + CStr(Cell.Value))
End Function
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel
How can I write loop for this:-
Sub format()
Dim X As Variant
X = range("M1")
X = Replace(X, "-", "", 1)
'The next line puts the cleaned up data back in J1
range("M1") = X
End Sub
Thank you all for your previous answers.
RE: Excel
--Scott
http://wertel.eng.pro
RE: Excel
Dim X As Variant
Dim mycell As Range
For Each mycell In Selection
X = mycell.Value
X = Replace(X, "-", "", 1)
'The next line puts the cleaned up data back in mycell
mycell.Value = X
Next mycell
End Sub
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel
Thank you for that post; Can I change "mycell" to a range;
For instance an entire column (m1:M10000). I am having trouble changing to this. Your help will be appreciated.
Regards
Swertel'
I use a lot excel and is the only software available to me_ Hence the persistence. Thanks for your reply.
Regards
RE: Excel
Try this:
Sub format()
Dim X As Variant
Dim mycell As Range
For Each mycell In Selection
X = Cells(m1:M10000).Value
X = Replace(X, "-", "", 1)
'The next line puts the cleaned up data back in mycell
mycell.Value = X
Next mycell
End Sub
RE: Excel
That addition seems to be inappropriate. I think I will need to refine. Thanks for the posting.
RE: Excel
CODE
Dim X As Variant
Dim mycell As Range
For Each mycell In Sheets("Sheet1").Range("A1:C10")
X = mycell.Value
X = Replace(X, "-", "", 1)
'The next line puts the cleaned up data back in mycell
mycell.Value = X
Next mycell
End Sub
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel
I will try that:
Regards
RE: Excel
Look in the FAQs for this subject. There's an appropriate subject there discussed in length.
RE: Excel
Now: I am trying to invoke this script whenever someone pastes something:
For instance:
I place 123-987-6541 in Cell A1; I want it to automatically chnage to "1239876541" with the dashes gone. All automatically without having to manually run the macro- i used the call on file command which seems to stay idle. I would appreciate if anyone could help on this issue.
Regards
RE: Excel
If you always right click to paste, you could event use the "beforerightclick" event.
RE: Excel
RE: Excel
CODE
format
Cancel = True ' Change to false to show right click menu
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
format
End Sub
Private Sub format()
Dim X As Variant
Dim mycell As Range
For Each mycell In Sheets("Sheet1").Range("A1:C10")
X = mycell.Value
X = Replace(X, "-", "", 1)
'The next line puts the cleaned up data back in mycell
mycell.Value = X
Next mycell
End Sub
RE: Excel
Going back I really think I need some kinda invoke script like
when Selection.paste in a range automatically run macro. See if you can point me into a direction. Thanks
RE: Excel
An alternate solution would be to replace ctrl-V with a macro.
Save this macro in the module1 section of the VBA editor. Then in excel, on the macro dialog box there is an options button that allows you to assign keys to the macro.
CODE
Dim X As Variant
Dim mycell As Range
ActiveSheet.Paste
For Each mycell In Selection
X = mycell.Value
X = Replace(X, "-", "", 1)
'The next line puts the cleaned up data back in mycell
mycell.Value = X
Next mycell
End Sub