Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: VBA Code to Multiple Specific Cells by Cell Value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code to Multiple Specific Cells by Cell Value

    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!

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,940
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Multiple Specific Cells by Cell Value

    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
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Multiple Specific Cells by Cell Value

    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.

    Quote Originally Posted by alansidman View Post
    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

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA Code to Multiple Specific Cells by Cell Value

    Quote Originally Posted by rbellavance22 View Post
    .. 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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Multiple Specific Cells by Cell Value

    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

    Quote Originally Posted by Peter_SSs View Post
    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 by rbellavance22; Sep 17th, 2019 at 11:07 AM.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA Code to Multiple Specific Cells by Cell Value

    Quote Originally Posted by rbellavance22 View Post
    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 by Peter_SSs; Sep 17th, 2019 at 06:36 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Multiple Specific Cells by Cell Value

    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.

    Quote Originally Posted by Peter_SSs View Post
    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)")

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,275
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code to Multiple Specific Cells by Cell Value

    How about
    Code:
    Sub IncreaseByPercentage()
      With Range("B14:D15")
        .Value = Evaluate("if({1},ceiling(A1*" & .Address & ",0.05))")
      End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Multiple Specific Cells by Cell Value

    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?

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,275
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code to Multiple Specific Cells by Cell Value

    You would have to do them separately, as evaluate doesn't seem to like non-contiguous ranges
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •