| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter number in cell and have it divide by 4
I have a cell that when I type the number for example 12 I want it to return 3
So the Number I type in divided by 4. I have some other cells I am going to do with the number 8 and so on. Thanks Cheyenne |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Enter number in cell and have it divide by 4
Chey,
This will require an even-driven (Worksheet_Change) macro. You'll have to put in in the workbook. Here's one that will divide anything in column B (column #2): Private Sub Worksheet_Change(ByVal Target As Range) Const ColumnNumber = 2 If Not Intersect(Target, Cells(1, ColumnNumber).EntireColumn) Is Nothing Then On Error GoTo errr If IsNumeric(Target.Value) Then Application.EnableEvents = False Target = Target / 4 Application.EnableEvents = True End If End If End errr: Application.EnableEvents = True MsgBox "Error: " & Err.Description End Sub It goes in the Sheet module for the sheet involved. Or you can put your original value in, and have another cell with a formula that produces the result you want: =A2/4 -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Chey" wrote in message ... I have a cell that when I type the number for example 12 I want it to return 3 So the Number I type in divided by 4. I have some other cells I am going to do with the number 8 and so on. Thanks Cheyenne |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Enter number in cell and have it divide by 4
I put it in the visual basic is that right?
If so It did not work I need to narrow it down to exactly a colum and a row. example C25 So when I type 12 in C25 it changes it to 3 in C26 it needs to divide by 8. Thanks for your time. Cheyenne "Earl Kiosterud" wrote: Chey, This will require an even-driven (Worksheet_Change) macro. You'll have to put in in the workbook. Here's one that will divide anything in column B (column #2): Private Sub Worksheet_Change(ByVal Target As Range) Const ColumnNumber = 2 If Not Intersect(Target, Cells(1, ColumnNumber).EntireColumn) Is Nothing Then On Error GoTo errr If IsNumeric(Target.Value) Then Application.EnableEvents = False Target = Target / 4 Application.EnableEvents = True End If End If End errr: Application.EnableEvents = True MsgBox "Error: " & Err.Description End Sub It goes in the Sheet module for the sheet involved. Or you can put your original value in, and have another cell with a formula that produces the result you want: =A2/4 -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Chey" wrote in message ... I have a cell that when I type the number for example 12 I want it to return 3 So the Number I type in divided by 4. I have some other cells I am going to do with the number 8 and so on. Thanks Cheyenne |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Enter number in cell and have it divide by 4
Chey,
First create a helper column with your numbers. Lets say it is column R. Copy this code: Private Sub Worksheet_Change(ByVal Target As Range) Const ColumnNumber = 18 If Not Intersect(Target, Cells(1, ColumnNumber).EntireColumn) Is Nothing Then On Error GoTo errr If IsNumeric(Target.Value) Then Application.EnableEvents = False Target = Target / 4 Application.EnableEvents = True End If End If End errr: Application.EnableEvents = True MsgBox "Error: " & Err.Description End Sub Go to your workbookright click on the tab of the sheet with your data and Paste the code. In Cell C1 type =R1 and copy down. In Cell C26 type =R26/2. Do same with the other cells where you want to divide by 8. Regards, Tim "Chey" wrote: I put it in the visual basic is that right? If so It did not work I need to narrow it down to exactly a colum and a row. example C25 So when I type 12 in C25 it changes it to 3 in C26 it needs to divide by 8. Thanks for your time. Cheyenne "Earl Kiosterud" wrote: Chey, This will require an even-driven (Worksheet_Change) macro. You'll have to put in in the workbook. Here's one that will divide anything in column B (column #2): Private Sub Worksheet_Change(ByVal Target As Range) Const ColumnNumber = 2 If Not Intersect(Target, Cells(1, ColumnNumber).EntireColumn) Is Nothing Then On Error GoTo errr If IsNumeric(Target.Value) Then Application.EnableEvents = False Target = Target / 4 Application.EnableEvents = True End If End If End errr: Application.EnableEvents = True MsgBox "Error: " & Err.Description End Sub It goes in the Sheet module for the sheet involved. Or you can put your original value in, and have another cell with a formula that produces the result you want: =A2/4 -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Chey" wrote in message ... I have a cell that when I type the number for example 12 I want it to return 3 So the Number I type in divided by 4. I have some other cells I am going to do with the number 8 and so on. Thanks Cheyenne |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Enter number in cell and have it divide by 4
Chey,
You have to say more than "did not work." Try this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("C25")) Is Nothing Then On Error GoTo errr If IsNumeric(Target.Value) Then Application.EnableEvents = False Target = Target / 4 Application.EnableEvents = True End If End If If Not Intersect(Target, Range("C26")) Is Nothing Then On Error GoTo errr If IsNumeric(Target.Value) Then Application.EnableEvents = False Target = Target / 8 Application.EnableEvents = True End If End If End errr: Application.EnableEvents = True MsgBox "Error: " & Err.Description End Sub -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "Chey" wrote in message ... I put it in the visual basic is that right? If so It did not work I need to narrow it down to exactly a colum and a row. example C25 So when I type 12 in C25 it changes it to 3 in C26 it needs to divide by 8. Thanks for your time. Cheyenne "Earl Kiosterud" wrote: Chey, This will require an even-driven (Worksheet_Change) macro. You'll have to put in in the workbook. Here's one that will divide anything in column B (column #2): Private Sub Worksheet_Change(ByVal Target As Range) Const ColumnNumber = 2 If Not Intersect(Target, Cells(1, ColumnNumber).EntireColumn) Is Nothing Then On Error GoTo errr If IsNumeric(Target.Value) Then Application.EnableEvents = False Target = Target / 4 Application.EnableEvents = True End If End If End errr: Application.EnableEvents = True MsgBox "Error: " & Err.Description End Sub It goes in the Sheet module for the sheet involved. Or you can put your original value in, and have another cell with a formula that produces the result you want: =A2/4 -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Chey" wrote in message ... I have a cell that when I type the number for example 12 I want it to return 3 So the Number I type in divided by 4. I have some other cells I am going to do with the number 8 and so on. Thanks Cheyenne |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Enter number in cell and have it divide by 4
Here is one method. You can do this without using macros by doing a little
bit of slight of hand... The jist of it is to hide the value that you enter by formatting it to not show and then use the camera object to show the results of a formula. Assuming you are dealing with cell A1 on Sheet1... For Sheet 1 A1 make the font colour the same as the background colour. Add the formula =Sheet1!A1/4 in cell A1 on Sheet 2. Select Tools - Options - View and uncheck Gridlines. Now follow the oddity outlined here... http://j-walk.com/ss/excel/odd/odd04.htm I like this method because you do not need to enable macros and the original value entered by the user is there in the cell (shows in the formula bar when the cell is selected). -- HTH... Jim Thomlinson "Chey" wrote: I have a cell that when I type the number for example 12 I want it to return 3 So the Number I type in divided by 4. I have some other cells I am going to do with the number 8 and so on. Thanks Cheyenne |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Enter number in cell and have it divide by 4
I tired this and it did not work twords my advantage.
Sorry Do you know any other techniques. "Jim Thomlinson" wrote: Here is one method. You can do this without using macros by doing a little bit of slight of hand... The jist of it is to hide the value that you enter by formatting it to not show and then use the camera object to show the results of a formula. Assuming you are dealing with cell A1 on Sheet1... For Sheet 1 A1 make the font colour the same as the background colour. Add the formula =Sheet1!A1/4 in cell A1 on Sheet 2. Select Tools - Options - View and uncheck Gridlines. Now follow the oddity outlined here... http://j-walk.com/ss/excel/odd/odd04.htm I like this method because you do not need to enable macros and the original value entered by the user is there in the cell (shows in the formula bar when the cell is selected). -- HTH... Jim Thomlinson "Chey" wrote: I have a cell that when I type the number for example 12 I want it to return 3 So the Number I type in divided by 4. I have some other cells I am going to do with the number 8 and so on. Thanks Cheyenne |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Enter number in cell and have it divide by 4
Nope... I have used both the technique that Earl suggested and the technique
that I showed successfully. -- HTH... Jim Thomlinson "Chey" wrote: I tired this and it did not work twords my advantage. Sorry Do you know any other techniques. "Jim Thomlinson" wrote: Here is one method. You can do this without using macros by doing a little bit of slight of hand... The jist of it is to hide the value that you enter by formatting it to not show and then use the camera object to show the results of a formula. Assuming you are dealing with cell A1 on Sheet1... For Sheet 1 A1 make the font colour the same as the background colour. Add the formula =Sheet1!A1/4 in cell A1 on Sheet 2. Select Tools - Options - View and uncheck Gridlines. Now follow the oddity outlined here... http://j-walk.com/ss/excel/odd/odd04.htm I like this method because you do not need to enable macros and the original value entered by the user is there in the cell (shows in the formula bar when the cell is selected). -- HTH... Jim Thomlinson "Chey" wrote: I have a cell that when I type the number for example 12 I want it to return 3 So the Number I type in divided by 4. I have some other cells I am going to do with the number 8 and so on. Thanks Cheyenne |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Enter number in cell and have it divide by 4
okay after talking with a co-worker we are streamlining it.
They will type a value in a field then if the amount is between 0-499 500-999 1000 and over If the amount falls between 0-499 divide the amount by 4--I know how to do this one 500-599 then divide by 8 I don't know how to do this one. I tired and no luck 1000 and over I can do this one also When I typed in 550 it still came out as zero, when I changed my signs around it didn't always work If(B10500<999,B10/8,0) This is what I tired. Thanks Cheyenne "Chey" wrote: I tired this and it did not work twords my advantage. Sorry Do you know any other techniques. "Jim Thomlinson" wrote: Here is one method. You can do this without using macros by doing a little bit of slight of hand... The jist of it is to hide the value that you enter by formatting it to not show and then use the camera object to show the results of a formula. Assuming you are dealing with cell A1 on Sheet1... For Sheet 1 A1 make the font colour the same as the background colour. Add the formula =Sheet1!A1/4 in cell A1 on Sheet 2. Select Tools - Options - View and uncheck Gridlines. Now follow the oddity outlined here... http://j-walk.com/ss/excel/odd/odd04.htm I like this method because you do not need to enable macros and the original value entered by the user is there in the cell (shows in the formula bar when the cell is selected). -- HTH... Jim Thomlinson "Chey" wrote: I have a cell that when I type the number for example 12 I want it to return 3 So the Number I type in divided by 4. I have some other cells I am going to do with the number 8 and so on. Thanks Cheyenne |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Enter number in cell and have it divide by 4
Not sure exactly what you want but something like this perhaps...
=if(b10<500, b10/4, if(b10<600, b10/8, if(b10<1000, b10/4, 0))) -- HTH... Jim Thomlinson "Chey" wrote: okay after talking with a co-worker we are streamlining it. They will type a value in a field then if the amount is between 0-499 500-999 1000 and over If the amount falls between 0-499 divide the amount by 4--I know how to do this one 500-599 then divide by 8 I don't know how to do this one. I tired and no luck 1000 and over I can do this one also When I typed in 550 it still came out as zero, when I changed my signs around it didn't always work If(B10500<999,B10/8,0) This is what I tired. Thanks Cheyenne "Chey" wrote: I tired this and it did not work twords my advantage. Sorry Do you know any other techniques. "Jim Thomlinson" wrote: Here is one method. You can do this without using macros by doing a little bit of slight of hand... The jist of it is to hide the value that you enter by formatting it to not show and then use the camera object to show the results of a formula. Assuming you are dealing with cell A1 on Sheet1... For Sheet 1 A1 make the font colour the same as the background colour. Add the formula =Sheet1!A1/4 in cell A1 on Sheet 2. Select Tools - Options - View and uncheck Gridlines. Now follow the oddity outlined here... http://j-walk.com/ss/excel/odd/odd04.htm I like this method because you do not need to enable macros and the original value entered by the user is there in the cell (shows in the formula bar when the cell is selected). -- HTH... Jim Thomlinson "Chey" wrote: I have a cell that when I type the number for example 12 I want it to return 3 So the Number I type in divided by 4. I have some other cells I am going to do with the number 8 and so on. Thanks Cheyenne |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Enter number in cell and have it divide by 4
Put in C1 this formula: =IF(AND(R1=500,R1<600),R1/2,R1) and copy down.
Tim "Chey" wrote: okay after talking with a co-worker we are streamlining it. They will type a value in a field then if the amount is between 0-499 500-999 1000 and over If the amount falls between 0-499 divide the amount by 4--I know how to do this one 500-599 then divide by 8 I don't know how to do this one. I tired and no luck 1000 and over I can do this one also When I typed in 550 it still came out as zero, when I changed my signs around it didn't always work If(B10500<999,B10/8,0) This is what I tired. Thanks Cheyenne |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| i want to enter more then 16 number in one cell without text forme | Excel Discussion (Misc queries) | |||
| Divide number and add remainder in another cell | Excel Discussion (Misc queries) | |||
| Divide one row over other row I dont wont to divide one number | Excel Discussion (Misc queries) | |||
| How do I enter a formula in a cell so that letters= a number i.e.. | Excel Discussion (Misc queries) | |||
| You Must Enter A Number In This Cell | Excel Discussion (Misc queries) | |||
Hybrid Mode
