Simple Division??

I have a spread sheet. I would like to take it and be able to put a number
in it and have it automatically give me a percentage. In other words column
C has to totals. Lets say C2 is 5 and I type in 1 into D2. Is there a way
to have it automatically change my 1 into 20%? I have to do this for columns
D through R and have it figure out a percentage based off the totals listed
in column C. I don't need to show "1" just the percentage of 1 and 5...
Confusing enough yet? Any help would be most appreciated

right click on the sheet tab and select view code.

Put in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 4 Then
v = Target.Offset(0, -1).Value
If IsNumeric(v) And IsNumeric(Target) Then
If v <> 0 Then
Application.EnableEvents = False
On Error Resume Next
v1 = CDbl(Target.Value) / CDbl(v)
If Not IsError(v1) Then
Target.Value = v1
Target.NumberFormat = "#.0%"
End If
Application.EnableEvents = True
End If
End If
End If
End Sub


--
Regards,
Tom Ogilvy

"Steve" <[email protected]> wrote in message
news:[email protected]...
> I have a spread sheet. I would like to take it and be able to put a
number
> in it and have it automatically give me a percentage. In other words
column
> C has to totals. Lets say C2 is 5 and I type in 1 into D2. Is there a
way
> to have it automatically change my 1 into 20%? I have to do this for
columns
> D through R and have it figure out a percentage based off the totals
listed
> in column C. I don't need to show "1" just the percentage of 1 and 5...
> Confusing enough yet? Any help would be most appreciated
>

Thanks!

I entered that in, and it doesn't seem to be working. Maybe I'm doing
something wrong. It keeps trying to put all of that program into a cell...
and lists it as information not a function

"Tom Ogilvy" wrote:

> right click on the sheet tab and select view code.
>
> Put in code like this:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If Target.Column = 4 Then
> v = Target.Offset(0, -1).Value
> If IsNumeric(v) And IsNumeric(Target) Then
> If v <> 0 Then
> Application.EnableEvents = False
> On Error Resume Next
> v1 = CDbl(Target.Value) / CDbl(v)
> If Not IsError(v1) Then
> Target.Value = v1
> Target.NumberFormat = "#.0%"
> End If
> Application.EnableEvents = True
> End If
> End If
> End If
> End Sub
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Steve" <[email protected]> wrote in message
> news:[email protected]...
> > I have a spread sheet. I would like to take it and be able to put a
> number
> > in it and have it automatically give me a percentage. In other words
> column
> > C has to totals. Lets say C2 is 5 and I type in 1 into D2. Is there a
> way
> > to have it automatically change my 1 into 20%? I have to do this for
> columns
> > D through R and have it figure out a percentage based off the totals
> listed
> > in column C. I don't need to show "1" just the percentage of 1 and 5...
> > Confusing enough yet? Any help would be most appreciated
> >
>
>
>

Give this a whirl

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
If Target.Column < 19 And Target.Column > 3 And _
IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Value = Target.Value / Target.Offset(0, -(Target.Column - 3))
End If

ErrorHandler:
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson


"Steve" wrote:

> I have a spread sheet. I would like to take it and be able to put a number
> in it and have it automatically give me a percentage. In other words column
> C has to totals. Lets say C2 is 5 and I type in 1 into D2. Is there a way
> to have it automatically change my 1 into 20%? I have to do this for columns
> D through R and have it figure out a percentage based off the totals listed
> in column C. I don't need to show "1" just the percentage of 1 and 5...
> Confusing enough yet? Any help would be most appreciated
>

The solution that I have given you here is VBA code so you need to put it in
the code behind the sheet. Your macros need to be enabled. (if they are not
select Tools -> macros-> Security -> Medium and then shut down your Excel and
re-open it). Now right click on the Tab of the sheet you want to use this on
and select View Code. Paste this in the code window that opens up and you are
good to go. That is the same procedure to use with Tom's code... I have not
looked at it too closely but Tom is not wrong very often so I would be
willing to guess that it works.
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> Give this a whirl
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ErrorHandler
> If Target.Column < 19 And Target.Column > 3 And _
> IsNumeric(Target.Value) Then
> Application.EnableEvents = False
> Target.Value = Target.Value / Target.Offset(0, -(Target.Column - 3))
> End If
>
> ErrorHandler:
> Application.EnableEvents = True
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Steve" wrote:
>
> > I have a spread sheet. I would like to take it and be able to put a number
> > in it and have it automatically give me a percentage. In other words column
> > C has to totals. Lets say C2 is 5 and I type in 1 into D2. Is there a way
> > to have it automatically change my 1 into 20%? I have to do this for columns
> > D through R and have it figure out a percentage based off the totals listed
> > in column C. I don't need to show "1" just the percentage of 1 and 5...
> > Confusing enough yet? Any help would be most appreciated
> >

It doesn't go in a cell. As I said, right click on the sheet tab and select
view code.

a module will appear. Paste the code in that, then return to excel. It is
set up to only work with entries made in column D.

--
Regards,
Tom Ogilvy


"Steve" <[email protected]> wrote in message
news:[email protected]...
> Thanks!
>
> I entered that in, and it doesn't seem to be working. Maybe I'm doing
> something wrong. It keeps trying to put all of that program into a
cell...
> and lists it as information not a function
>
> "Tom Ogilvy" wrote:
>
> > right click on the sheet tab and select view code.
> >
> > Put in code like this:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Count > 1 Then Exit Sub
> > If Target.Column = 4 Then
> > v = Target.Offset(0, -1).Value
> > If IsNumeric(v) And IsNumeric(Target) Then
> > If v <> 0 Then
> > Application.EnableEvents = False
> > On Error Resume Next
> > v1 = CDbl(Target.Value) / CDbl(v)
> > If Not IsError(v1) Then
> > Target.Value = v1
> > Target.NumberFormat = "#.0%"
> > End If
> > Application.EnableEvents = True
> > End If
> > End If
> > End If
> > End Sub
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Steve" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a spread sheet. I would like to take it and be able to put a
> > number
> > > in it and have it automatically give me a percentage. In other words
> > column
> > > C has to totals. Lets say C2 is 5 and I type in 1 into D2. Is there
a
> > way
> > > to have it automatically change my 1 into 20%? I have to do this for
> > columns
> > > D through R and have it figure out a percentage based off the totals
> > listed
> > > in column C. I don't need to show "1" just the percentage of 1 and
5...
> > > Confusing enough yet? Any help would be most appreciated
> > >
> >
> >
> >