Consulting

Results 1 to 8 of 8

Thread: VBA adding numbers up to a certain range

  1. #1

    VBA adding numbers up to a certain range

    hello everyone,

    i'm new in vba and i will appreciate any help at all. i want to start adding from the top, numbers (thousands of numbers) from a column up to a certain given range and then add the next available values up to the same range and go on ..
    the table looks like this and maybe this can be the results from the VBA code

    type NUMBERS FROM TO RESULTS SUM
    number1 10 90 100 number 1 and number 2 and number 3 and number 5 and number 6 and number 13 100
    number2 20 number 4 and number 7 100
    number3 30 number 8 and number 9 100
    number4 50 number 10 and number 11 and number 14 and number 18 100
    number5 20 number 12 and number 15 and number 17 100
    number6 10 number 16 and number 19 100
    number7 50 number 20 and number 21 100
    number8 60 number 22 and number 24 90
    number9 40 number 23 70
    number10 50
    number11 20
    number12 50
    number13 10
    number14 20
    number15 30
    number16 50
    number17 20
    number18 10
    number19 50
    number20 60
    number21 40
    number22 50
    number23 70
    number24 40

    Thanks

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Sotos View Post
    type NUMBERS FROM TO RESULTS SUM
    number1 10 90 100 number 1 and number 2 and number 3 and number 5 and number 6 and number 13 100
    Why does this result include number 13 when 90 (the lower target) was reached by number 6?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Hi p45cal

    Good question..well this would be the optimum..to reach the higher level..but i dont care if it finds first 90 and then start a new calculation. We can then change the range to From 100 to 100..
    Do youhave any suggestions??

    Thanks

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Button to click in attached.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    p45cal you are a guru!!!it works great!!
    i want to change the SceValues = Range("A2:B50").Value to make it dynamic so i can add as much numbers as i want without needing to change the range.
    i tried SceValues = Range("A2:B").Value but i had an error. also i have some other questions in order for me to understand the logic because i'missing some parts.
    can i send them to you?
    also is there somewhere i can mark/range your solution?

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    try ONE of these lines:
    lr = Range("A1").End(xlDown).Row
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    followed by:
    SceValues = Range("A2:B" & lr).Value
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    yes correct

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Sotos View Post
    yes correct
    Yes, it was lower case LR not IR (abbreviating last row)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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