Change range of values in one command button click macro

DriedMangoes

New Member
Joined
Jun 3, 2018
Messages
3
Please be nice to me, I'm trying to learn VBA/macros by my own again after 15 years of not doing it :(

I know this is basic but huhuhu anyway.. I know how to change a value of a cell by a command button, but how do I change all of those values in one click of the command button. See my attached picture.



So I want to change the values of A1:I11 to become less 5% of their original value by clicking the "LESS 5%" button, and by clicking "Original Value", they will return to the original value I placed.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,491
Office Version
365, 2010
Platform
Windows, Mobile
One way...

Code:
    Application.ScreenUpdating = False
    Range("K1").Value = "0.95"
    Range("K1").Copy
    Range("A1:I11").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
    With Application
        .CutCopyMode = False
        Range("K1").ClearContents
        .ScreenUpdating = True
    End With
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
Another way
Code:
Sub Less5()
With Range("A1:I11")
   .Value = Evaluate(Replace("if(@<>"""",.95*@,"""")", "@", .Address))
End With
End Sub
 

DriedMangoes

New Member
Joined
Jun 3, 2018
Messages
3
Wow I have never encountered this before, it worked, thank you! Would it be okay if I asked what "k1" stands for?
And also, is there a way for the values to return to its original value, say, if I clicked "LESS 5%", 900 would become 855, then if I clicked "ORIGINAL VALUE", 855 would go back to 900. Of course I cannot change "0.95" to "1.05" and create another command as it will only pile on top (as in 900*0.95*1.05).
 

DriedMangoes

New Member
Joined
Jun 3, 2018
Messages
3
Hello Fluff, I am years behind with all these stuff so pardon me if I ask silly questions like.. do I replace @ with a range? and what do I put inside the quotation marks?

Another way
Code:
Sub Less5()
With Range("A1:I11")
   .Value = Evaluate(Replace("if(@<>"""",.95*@,"""")", "@", .Address))
End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
do I replace @ with a range? and what do I put inside the quotation marks?
No, you don't need to change anything. Just run it as-is.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
And to add return to the original value try
Code:
Sub Add5()
With Range("A1:I11")
   .Value = Evaluate(Replace("if(@<>"""",@/.95,"""")", "@", .Address))
End With
End Sub
 

Forum statistics

Threads
1,082,281
Messages
5,364,216
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top