Solved

Change by Amount

Posted on 2013-01-20
8
280 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

622 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