VBA adding numbers up to a certain range

Printable View

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

05-21-2019, 08:16 AM
p45cal

Quote:

Originally Posted by Sotos

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?
05-21-2019, 11:47 AM
Sotos

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
05-21-2019, 12:18 PM
p45cal

1 Attachment(s)

Button to click in attached.
05-21-2019, 02:08 PM
Sotos

p45cal you are a guru!!!it works great!! :yes
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?
05-21-2019, 02:23 PM
p45cal

try ONE of these lines:

Code:

lr = Range("A1").End(xlDown).Row lr = Cells(Rows.Count, "A").End(xlUp).Row

followed by:

Code:

SceValues = Range("A2:B" & lr).Value
05-21-2019, 02:40 PM
Sotos

yes correct
05-21-2019, 02:56 PM
p45cal

Quote:

Originally Posted by Sotos

yes correct

Yes, it was lower case LR not IR (abbreviating last row)

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