×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

RE: Excel

No and yes.

RE: Excel

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.

RE: Excel

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: Eng-Tips.com Forum Policies

RE: Excel

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
 

RE: Excel

I still say the objective here is a little fuzzy.

Quote:

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.

RE: Excel

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

CODE

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.

RE: Excel

(OP)
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.

RE: Excel

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

RE: Excel

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.

RE: Excel

(OP)
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

 

RE: Excel

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
 

RE: Excel

(OP)
PST09

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

RE: Excel

How about:

CODE

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.

RE: Excel

(OP)
Thanks Pete.

I will try that:

Regards

RE: Excel

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

RE: Excel

(OP)
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
 

RE: Excel

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.

RE: Excel

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

RE: Excel

add the following code to the sheet1 object in VBA

CODE

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

RE: Excel

I did try it bit seems like I am not doing it rite, will post comments after further testing.

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

I haven't found anything in excel that captures paste events.  In the user forms, there is a BeforeDropOrPaste but I am not sure that would work for you.

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

Sub RemoveDashes()
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources