Solved

Change by Amount

Posted on 2013-01-20
8
265 Views
Last Modified: 2013-01-21
Hello All,

I am looking for a quick VBA to do this. Whenever users put a percentage number in cell F1, then it increases or decreases values (by a percentage) in column “Dollar” and puts the corresponding result in “changedDollar” column
For example: if user put 4 in cell F1, then F5 = 45 + 45*.04 =46.8
For example: if user put - 4 in cell F1, then F5 = 45 -45*.04 = 43.2

If the total after percentage decrease, becomes a negative number, then that total becomes zero
For example if the percentage decrease is -1.5, then
F5 = 45-(45*1.5) = -22.5
This is not allowed as changedDollar CANT be negative. Hence in that case, changedDollar = 0

F5 = 45-(45*1.5) becomes 0
changeByPercent.xlsx
0
Comment
Question by:Rayne
  • 6
  • 2
8 Comments
 

Author Comment

by:Rayne
ID: 38799923
thank you
0
 

Author Comment

by:Rayne
ID: 38799926
Also this change will register in cells that are visible only, not hidden cell rows
0
 
LVL 5

Expert Comment

by:Harsem
ID: 38800734
Hello,

could you please clarify one thing for me. Your example lsits a value of -1.5, yet this is not a percentage in your example. It would be my understanding that it should read:
F5 = 45-(45*0.015) = 44.325

The only time a value could be negative would be when you enter in a value less than -100.

Is there any reason you want to use VBA? YOu could just use calculations rather than VBA Code.

I have attached an Excel file using a simple if (for the cases where F1 is less than 100) plus a calculation for the total amount for each column.
changeByPercent.xlsx
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Rayne
ID: 38801982
Hello Harsem,

thank you for your suggestion :)

Since the table that you are seeing would actually be a much bigger table with filter check boxes above and users would be filtering that huge table to thier selected rows only and then doing the percent changes on those rows only ...that's why I asked for VBA or whichever way to do this calculation Only to those visible rows, not hidden rows....
let me know if that makes sense...

thank you
0
 

Author Comment

by:Rayne
ID: 38801994
Harsem,

Also the calculated numbers need to be then in xlvalues, not remain as formulas
0
 

Author Comment

by:Rayne
ID: 38803213
Any experts are Welcome here :)
0
 
LVL 5

Accepted Solution

by:
Harsem earned 500 total points
ID: 38803397
Hello,

I believe this will do what you are looking for:

This will start at F5 and go to F65500 (you can shorten or increase this according to your needs, and then ONLY select the visible cells.

Next it will add or remove the percentage value from F1, if it is less than -100% then it will put in 0.

Finally, it will only leave you the values, and not the formula in column F.

Sub EditFilteredRange()
    Dim rng As Range
    Set rng = Range("F5", Range("F65500").End(xlUp)).SpecialCells(xlCellTypeVisible)
    For Each cell In rng
        cell.Value = "=IF(R1C6<-100,0,RC[-1]+(RC[-1]*(R1C6/100)))"
    Next cell
   
    rng.Copy
    rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

End Sub
0
 

Author Comment

by:Rayne
ID: 38804042
Perfect Harsem,
That works like a Charm :)

Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

My experience with Windows 10 over a one year period and suggestions for smooth operation
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question