TechArena Community How to change a percentage in a single cell ?

#1
13-02-2009
 Member Join Date: Feb 2008 Posts: 767
How to change a percentage in a single cell ?

I want to just assign a percentage reduction on numbers placed in cells. The list box with the % (5, 10 ...) is well established and I have my number in F9. I would now like to change, I choose in my list the percentage that I would grant that the same number in F9 is adapted with the percentage requested.
Here's what I did, but I immediately 0 in F9, but if I do the result in another cell, it goes well, but this is not the goal.

Code:
```Private Sub ComboBox1_Change ()
If ComboBox1.Value = "0" Then
Range ( "F9"). Formula = "= F9-(F9 * 0%)"
ElseIf ComboBox1.Value = "5" Then
Range ( "F9"). Formula = "= F9-(F9 * 5%)"
ElseIf ComboBox1.Value = "10" Then
Range ( "F9"). Formula = "= F9-(F9 * 10%)"
ElseIf ComboBox1.Value = "15" Then
Range ( "F9"). Formula = "= F9-(F9 * 15%)"
End If
End Sub```
So is there a way to put this number in buffer or else?

BTW I am not in a userform! but directly on the sheet excel
#2
13-02-2009
 Member Join Date: May 2008 Posts: 804
Re: How to change a percentage in a single cell ?

Normal, you say that the result of your cell = your cell less / plus the percentage.

Either you pass through an intermediate step like:
Depart | Percentage | Results

Or, you can not go through a formula (as the cell source is also the resulting cell) and that you will result in the cell, vba read:

Code:
```Const val1 = 150
Private Sub ComboBox1_Change ()
Range ( "F9"). Val1 = value * (1 - CDbl (ComboBox1.Value) / 100) 'no need for you if it takes the direct value in the combobox
End Sub```
The disadvantage is that you stored in a constant value, and not in your cell.

But the best is still to pass through an intermediate step because you want to keep in memory the number of departure.

Best regards
#3
13-02-2009
 Member Join Date: Feb 2008 Posts: 767
Re: How to change a percentage in a single cell ?

yes indeed for the intermediate stage as if I calculate the 10% of the number then I want to deliver 0% or 15%, it is calculated on the number of start ...
But, how do I write an intermediate step?

I thought to copy the number in another cell is hiding? (if possible)
#4
13-02-2009
 Member Join Date: May 2008 Posts: 804
Re: How to change a percentage in a single cell ?

Uh you watched my code?
There is a constant. So if you had fun changing a number in a hidden cell, all the change directly in your code.
#5
13-02-2009
 Member Join Date: Feb 2008 Posts: 767
Re: How to change a percentage in a single cell ?

It's good I found, thank you
I copied my number to another cell and it goes perfectly.

 Tags: