Change by Amount

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
RayneAsked:
Who is Participating?
 
HarsemConnect With a Mentor Commented:
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
 
RayneAuthor Commented:
thank you
0
 
RayneAuthor Commented:
Also this change will register in cells that are visible only, not hidden cell rows
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
HarsemCommented:
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
 
RayneAuthor Commented:
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
 
RayneAuthor Commented:
Harsem,

Also the calculated numbers need to be then in xlvalues, not remain as formulas
0
 
RayneAuthor Commented:
Any experts are Welcome here :)
0
 
RayneAuthor Commented:
Perfect Harsem,
That works like a Charm :)

Thanks
0
All Courses

From novice to tech pro — start learning today.