Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
typing in a number and converting it something else
I am tring to make a form that I will be able to enter in
a number and the formula will do the math. I also want it to out put a negitive or positive number. example: i enter in the number 15mm into the cell and it out puts X0001.693 So this is what I have so far in the customize cell featu =25.4/X0000.000;X-0000.000 Can someone please help. Thanks in advanced, Darrell |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
typing in a number and converting it something else
you really can't do that type of math just using a format.
Also, entering 15mm will make this a string and very little can be done with formatting to affect a string. Do you want an event macro that will make the change? -- Regards, Tom Ogilvy "Darrell" wrote in message ... I am tring to make a form that I will be able to enter in a number and the formula will do the math. I also want it to out put a negitive or positive number. example: i enter in the number 15mm into the cell and it out puts X0001.693 So this is what I have so far in the customize cell featu =25.4/X0000.000;X-0000.000 Can someone please help. Thanks in advanced, Darrell |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
typing in a number and converting it something else
If that would work that would be great, however I do not
know anything about event macros. Darrell -----Original Message----- you really can't do that type of math just using a format. Also, entering 15mm will make this a string and very little can be done with formatting to affect a string. Do you want an event macro that will make the change? -- Regards, Tom Ogilvy "Darrell" wrote in message ... I am tring to make a form that I will be able to enter in a number and the formula will do the math. I also want it to out put a negitive or positive number. example: i enter in the number 15mm into the cell and it out puts X0001.693 So this is what I have so far in the customize cell featu =25.4/X0000.000;X-0000.000 Can someone please help. Thanks in advanced, Darrell . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
typing in a number and converting it something else
also i would actually prefer to just enter in 15 not
15mm. I don't know if that will make a difference. Thanks again, Darrell -----Original Message----- If that would work that would be great, however I do not know anything about event macros. Darrell -----Original Message----- you really can't do that type of math just using a format. Also, entering 15mm will make this a string and very little can be done with formatting to affect a string. Do you want an event macro that will make the change? -- Regards, Tom Ogilvy "Darrell" wrote in message ... I am tring to make a form that I will be able to enter in a number and the formula will do the math. I also want it to out put a negitive or positive number. example: i enter in the number 15mm into the cell and it out puts X0001.693 So this is what I have so far in the customize cell featu =25.4/X0000.000;X-0000.000 Can someone please help. Thanks in advanced, Darrell . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
typing in a number and converting it something else
Assume you only want this to occur in one column.
Right click on the sheet tab and select view code. Paste this code in the module: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrHandler If Target.Column = 3 Then If Target.Count 1 Then Exit Sub If Not IsEmpty(Target.Value) Then If IsNumeric(Target.Value) Then sValue = Format(25.4 / Target.Value, "X0000.000;X-0000.000") Application.EnableEvents = False Target.Value = sValue End If End If End If ErrHandler: Application.EnableEvents = True End Sub As written, it reformats any numeric entry in column C. Change the 3 to reflect the column where you want this behavior (A - 1, B - 2, etc). -- Regards, Tom Ogilvy "Darrell" wrote in message ... also i would actually prefer to just enter in 15 not 15mm. I don't know if that will make a difference. Thanks again, Darrell -----Original Message----- If that would work that would be great, however I do not know anything about event macros. Darrell -----Original Message----- you really can't do that type of math just using a format. Also, entering 15mm will make this a string and very little can be done with formatting to affect a string. Do you want an event macro that will make the change? -- Regards, Tom Ogilvy "Darrell" wrote in message ... I am tring to make a form that I will be able to enter in a number and the formula will do the math. I also want it to out put a negitive or positive number. example: i enter in the number 15mm into the cell and it out puts X0001.693 So this is what I have so far in the customize cell featu =25.4/X0000.000;X-0000.000 Can someone please help. Thanks in advanced, Darrell . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
typing in a number and converting it something else
Another thing in the 2 colum i would like it to come out
as X0000.00 ;-X0000.00 AND IN THE 3 COLUM i would like it to come out as Y0000.00 ;-Y0000.00. Darrell -----Original Message----- Assume you only want this to occur in one column. Right click on the sheet tab and select view code. Paste this code in the module: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrHandler If Target.Column = 3 Then If Target.Count 1 Then Exit Sub If Not IsEmpty(Target.Value) Then If IsNumeric(Target.Value) Then sValue = Format(25.4 / Target.Value, "X0000.000;X- 0000.000") Application.EnableEvents = False Target.Value = sValue End If End If End If ErrHandler: Application.EnableEvents = True End Sub As written, it reformats any numeric entry in column C. Change the 3 to reflect the column where you want this behavior (A - 1, B - 2, etc). -- Regards, Tom Ogilvy "Darrell" wrote in message ... also i would actually prefer to just enter in 15 not 15mm. I don't know if that will make a difference. Thanks again, Darrell -----Original Message----- If that would work that would be great, however I do not know anything about event macros. Darrell -----Original Message----- you really can't do that type of math just using a format. Also, entering 15mm will make this a string and very little can be done with formatting to affect a string. Do you want an event macro that will make the change? -- Regards, Tom Ogilvy "Darrell" wrote in message ... I am tring to make a form that I will be able to enter in a number and the formula will do the math. I also want it to out put a negitive or positive number. example: i enter in the number 15mm into the cell and it out puts X0001.693 So this is what I have so far in the customize cell featu =25.4/X0000.000;X-0000.000 Can someone please help. Thanks in advanced, Darrell . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting a positive number to neg number in spreadsheets | Excel Discussion (Misc queries) | |||
Typing a credit card number in a cell | Excel Discussion (Misc queries) | |||
Converting a negative number to a positive number | Excel Discussion (Misc queries) | |||
Can I auto fill a cell in one worksheet by typing a code number | Excel Worksheet Functions | |||
how to get a name just by typing a number? | Excel Discussion (Misc queries) |