Excel 4

[drfads07]

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

 

[MintJulep]

No and yes.

 

[electricpete]

I don't understand the question. You want to store/preserve your data as text including embedded dashes. And you want to dispaly in the same cell a number corresponding to the text without the dashes? To me sounds impossible or very difficult.

If you have another cell to spare, it would be easy.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

Excel, by itself, can display either formulas or results, on a single sheet.

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

 

[cherrypicker]

In simple terms you can achieve your aim, as an example Cell A1 (containing 1) added to Cell B1 (containing 2), using the following syntax by entering the formula in Cell C1 :-

="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

 

[electricpete]

I still say the objective here is a little fuzzy.

Is there a way where you could display both the formula and its out put in the same cell.

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.

 

[electricpete]

Whoops - used a different function name and function assignment. Try it this way:

Function MYDISPLAY(Cell As Range)
   MYDISPLAY = CStr(Cell.Formula + " = " + CStr(Cell.Value))
End Function

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[drfads07]

I have a VB script as follows:

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.

 

[swertel]

Sounds to me like Excel is not the ideal tool for the job. Instead of using a hammer, try MathCAD or some other mathematical software instead. Is that a possibility?

--Scott

http://wertel.eng.pro

 

[electricpete]

Sub format()
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.

 

[drfads07]

Electric Pete

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

 

[PST09]

drfads07 i think that you can assign a range of cells to the valvue my.cell.

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

 

[drfads07]

PST09

That addition seems to be inappropriate. I think I will need to refine. Thanks for the posting.

 

[electricpete]

How about:

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

Change "Sheet1" to match your sheetname and "A1:C10" to your range.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[drfads07]

Thanks Pete.

I will try that:

Regards

 

[yakpol]

drfads07,
Look in the FAQs for this subject. There's an appropriate subject there discussed in length.

 

[drfads07]

Sweet, thanks for all your help guys.
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

 

[bacon4life]

You may be able to use the "worksheet_change" or the "worksheet_calculate" events to format the most recently pasted cell. Are there cells in the worksheet that you don't want formatted?

If you always right click to paste, you could event use the "beforerightclick" event.

 

[drfads]

I guess, I could try those- How do you use the "Beforeclick" command.

 

[bacon4life]

add the following code to the sheet1 object in VBA

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
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