Help with percent formula beginner

Hi, have a cell (A1) with $39.99. I want a cell (B1) were I can vary
10%, 20% etc and have that effect (A1). So If I put in 10% A1 would be
$36.00 (percent decrease). Can you please help me out with the formula?

Also it seems like if I type in % in a cell and I delete it and type
another number and I don't want a % in there it gives it to me anyway.
Can I make this stop?

Thanks so much

Ted

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
With Target
.Offset(0, -1).Value = Round(.Offset(0, -1).Value * (1 -
..Value), 2)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ted" <[email protected]> wrote in message
news:[email protected]...
> Hi, have a cell (A1) with $39.99. I want a cell (B1) were I can vary
> 10%, 20% etc and have that effect (A1). So If I put in 10% A1 would be
> $36.00 (percent decrease). Can you please help me out with the formula?
>
> Also it seems like if I type in % in a cell and I delete it and type
> another number and I don't want a % in there it gives it to me anyway.
> Can I make this stop?
>
> Thanks so much
>
> Ted
>

Thanks, so quick too. It works! Although I need $39.99 to be constant.
Like if I put 10% in it goes to $36.00. But if I put in 50% in place of
and after 10% it goes to 18 instead of $20.00. Any ideas?

Ted

Bob Phillips wrote:
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
> With Target
> .Offset(0, -1).Value = Round(.Offset(0, -1).Value * (1 -
> .Value), 2)
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Ted" <[email protected]> wrote in message
> news:[email protected]...
> > Hi, have a cell (A1) with $39.99. I want a cell (B1) were I can vary
> > 10%, 20% etc and have that effect (A1). So If I put in 10% A1 would be
> > $36.00 (percent decrease). Can you please help me out with the formula?
> >
> > Also it seems like if I type in % in a cell and I delete it and type
> > another number and I don't want a % in there it gives it to me anyway.
> > Can I make this stop?
> >
> > Thanks so much
> >
> > Ted
> >

Try this variation

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
With Target
.Offset(0, -1).Value = 39.99* (1 - .Value), 2)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ted" <[email protected]> wrote in message
news:[email protected]...
> Thanks, so quick too. It works! Although I need $39.99 to be constant.
> Like if I put 10% in it goes to $36.00. But if I put in 50% in place of
> and after 10% it goes to 18 instead of $20.00. Any ideas?
>
> Ted
>
> Bob Phillips wrote:
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
> > With Target
> > .Offset(0, -1).Value = Round(.Offset(0, -1).Value * (1 -
> > .Value), 2)
> > End With
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Ted" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi, have a cell (A1) with $39.99. I want a cell (B1) were I can vary
> > > 10%, 20% etc and have that effect (A1). So If I put in 10% A1 would be
> > > $36.00 (percent decrease). Can you please help me out with the
formula?
> > >
> > > Also it seems like if I type in % in a cell and I delete it and type
> > > another number and I don't want a % in there it gives it to me anyway.
> > > Can I make this stop?
> > >
> > > Thanks so much
> > >
> > > Ted
> > >
>

Hi, unfortunately receiving a compile error: Expected end of statement
at the , before 2).

Ted

Sorry, should have tested it.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
With Target
.Offset(0, -1).Value = Round(39.99 * (1 - .Value), 2)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ted" <[email protected]> wrote in message
news:[email protected]...
> Hi, unfortunately receiving a compile error: Expected end of statement
> at the , before 2).
>
> Ted
>

Perfect! Thanks you so much. Your a life safer.

Ted