Enter number in cell and have it divide by 4

[Guest]

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

 

[Earl Kiosterud]

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...

 

[Guest]

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

 

[Guest]

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

 

[Guest]

I tired this and it did not work twords my advantage.

Sorry

Do you know any other techniques.

 

[Guest]

Nope... I have used both the technique that Earl suggested and the technique
that I showed successfully.

 

[Guest]

Chey,

First create a helper column with your numbers. Let’s 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 workbook>right 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

 

[Guest]

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(B10>500<999,B10/8,0)

This is what I tired.

Thanks
Cheyenne

 

[Guest]

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

 

[Earl Kiosterud]

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


-----------------------------------------------------------------------

 

[Guest]

Put in C1 this formula: =IF(AND(R1>=500,R1<600),R1/2,R1) and copy down.

Tim