Solved

Change by Amount

Posted on 2013-01-20
8
270 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
[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
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

749 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