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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,586
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. 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
50,557
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
50,557
Office Version
  1. 365
Platform
  1. 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
50,557
Office Version
  1. 365
Platform
  1. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,102
Messages
5,570,222
Members
412,310
Latest member
ForceFollower
Top