Solved

# Change by Amount

Posted on 2013-01-20
280 Views
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
Question by:Rayne
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 2

Author Comment

ID: 38799923
thank you
0

Author Comment

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

LVL 5

Expert Comment

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

Author Comment

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

ID: 38801994
Harsem,

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

Author Comment

ID: 38803213
Any experts are Welcome here :)
0

LVL 5

Accepted Solution

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

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

Thanks
0

## Featured Post

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.