VBA Code to Multiple Specific Cells by Cell Value

rbellavance22

New Member
Joined
Aug 8, 2019
Messages
14
Hello,

I have a worksheet with various dollar amounts in multiple cells.

I would like to come up with a VBA Code to increase specific cells (B14, C14, D14, B15, C15, D15) by a percentage that is set by a cell value (cell A1).

Any help would be greatly appreciated!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Code:
Option Explicit


Sub test1()
    Dim m As Variant
    Dim c As Range
    Dim rng As Range
    m = Range("A1")
    Set rng = Range("B14:D14", "B15:D15")
    For Each c In rng
        c = c * m
    Next c
End Sub
 
Upvote 0
This is great. Thank you so much.

One quick question, since this a monetary value is there anyway to round up to the nearest nickel?

It standard formulas, it is typically =CEILING(sum(X*X),0.05), I'm just not sure where it goes here.

Code:
Option Explicit


Sub test1()
    Dim m As Variant
    Dim c As Range
    Dim rng As Range
    m = Range("A1")
    Set rng = Range("B14:D14", "B15:D15")
    For Each c In rng
        c = c * m
    Next c
End Sub
 
Upvote 0
.. increase specific cells ... by a percentage that is set by a cell value (cell A1).
I take that to mean that A1 might contain 10% and in that case if B14 contains 200 you want to change it to 220.
If that is so, try
Code:
Sub IncreaseByPercentage()
  With Range("B14:D15")
    .Value = Evaluate("ceiling((1+A1)*" & .Address & ",0.05)")
  End With
End Sub
 
Upvote 0
Thank you very much for the response. I am almost there.

It is rounding perfectly, however it is returning B14, C14 and D15 back with the same number.

The current values (after slight adjustments) are:
A1's value is 1.04
B14' value is 17
C14's value is 10
D14's value is 8

The goal is to change the values to:
B14: 17.70
C14: 10.4
D14: 8.35

I take that to mean that A1 might contain 10% and in that case if B14 contains 200 you want to change it to 220.
If that is so, try
Code:
Sub IncreaseByPercentage()
  With Range("B14:D15")
    .Value = Evaluate("ceiling((1+A1)*" & .Address & ",0.05)")
  End With
End Sub
 
Last edited:
Upvote 0
A1's value is 1.04
OK, I didn't know that you already had the 1.04 and thought you probably had just 0.04 so I added the 1 in my code. :)

Never-the-less my code should not have returned the same number back in B14:D15 (unless they already contained zero)

Try the replacement line:
Code:
.Value = Evaluate("ceiling(A1*" & .Address & ",0.05)")
 
Last edited:
Upvote 0
Thank you very much for the update.

Unfortunately when I run it, I am still getting the same values. B14 is perfect, however the other cells are returning the same value as B14.

OK, I didn't know that you already had the 1.04 and thought you probably had just 0.04 so I added the 1 in my code. :)

Never-the-less my code should not have returned the same number back in B14:D15 (unless they already contained zero)

Try the replacement line:
Code:
.Value = Evaluate("ceiling(A1*" & .Address & ",0.05)")
 
Upvote 0
How about
Code:
Sub IncreaseByPercentage()
  With Range("B14:D15")
    .Value = Evaluate("if({1},ceiling(A1*" & .Address & ",0.05))")
  End With
End Sub
 
Upvote 0
Great, thank you for your help.

One last thing: if I wanted to add two additional cells, C45 and D81, how would I do that?
 
Upvote 0
You would have to do them separately, as evaluate doesn't seem to like non-contiguous ranges
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top