Search for a sum!

[Luciano]

Dear all,
I am looking to consecutively sum the numbers in rows of a column with thousands of numbers until their sum is an exact expected value. Thus if the value of the sum is higher than that expected, the macro must start the sum in the following row and look for the expected value again. The macro must look for all situation in the column for which the sum is the searched value.. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cells "A:A" and that the detected expected values could be listed in "B:B", for example, as following small example:

Searched sum: 23

5.0
18.0 23
12.0
11.0 23
6.0
1.0
11.0
24.0
11.0
6.0
6.0 23
12.0
6.0
5.0 23
12.0 23
18.0
6.0
6.0
5.0

Thanks in advance,
Luciano

 

[Claus Busch]

Hi Luciano,

Am Thu, 19 Dec 2013 17:12:24 -0800 (PST) schrieb Luciano:

Searched sum: 23

5.0
18.0 23
12.0
11.0 23
6.0
1.0
11.0
24.0
11.0
6.0
6.0 23
12.0
6.0
5.0 23
12.0 23
18.0
6.0
6.0
5.0

that you can see what range makes the sum you get a formula in B:

Sub Test()
Dim i As Long
Dim j As Long
Dim LRow As Long
Dim mySum As Double

Const Res = 23

i = 1
LRow = Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To LRow
mySum = mySum + Cells(j, 1)
If mySum + Cells(i, 1) = Res Then
Cells(j, 1).Offset(, 1).Formula = _
"=SUM(" & Cells(i, 1).Address & ":" & Cells(j, 1) _
.Address & ")"
i = i + 1
j = i
mySum = 0
ElseIf mySum + Cells(i, 1) > Res Then
i = i + 1
j = i
mySum = 0
End If
Next
End Sub


Regards
Claus B.

 

[Luciano]

Em quinta-feira, 19 de dezembro de 2013 22h12min24s UTC-3, Luciano escreveu:

Dear all,

I am looking to consecutively sum the numbers in rows of a column with thousands of numbers until their sum is an exact expected value. Thus if the value of the sum is higher than that expected, the macro must start the sumin the following row and look for the expected value again. The macro mustlook for all situation in the column for which the sum is the searched value. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cells "A:A" and thatthe detected expected values could be listed in "B:B", for example, as following small example:



Searched sum: 23



5.0

18.0 23

12.0

11.0 23

6.0

1.0

11.0

24.0

11.0

6.0

6.0 23

12.0

6.0

5.0 23

12.0 23

18.0

6.0

6.0

5.0



Thanks in advance,

Luciano

Dear Claus,
Thank you very much! It is exactly this that I was trying. However, In somecases my row numbers are not integers but I would like that the routine could run without consider the decimal places. How could I do that? In addition, is it possible that the searched sum could be a number typed on a cell like D1?
Thanks in advance,
Luciano

 

[Claus Busch]

Hi Luciano,

Am Fri, 20 Dec 2013 01:22:09 -0800 (PST) schrieb Luciano:

It is exactly this that I was trying. However, In some cases my row numbers are not integers but I would like that the routine could run without consider the decimal places. How could I do that? In addition, is it possible that the searched sum could be a number typed on a cell like D1?

then try:

Sub Test()
Dim i As Long
Dim j As Long
Dim LRow As Long
Dim mySum As Double
Dim Res As Double

i = 1
Res = Range("D1")
LRow = Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To LRow
mySum = mySum + Round(Cells(j, 1), 0)
If mySum + Int(Cells(i, 1)) = Res Then
Cells(j, 1).Offset(, 1).Formula = _
"=SUM(" & Cells(i, 1).Address & ":" & Cells(j, 1) _
.Address & ")"
i = i + 1
j = i
mySum = 0
ElseIf mySum + Round(Cells(i, 1), 0) > Res Then
i = i + 1
j = i
mySum = 0
End If
Next
End Sub


Regards
Claus B.

 

[Luciano]

Em quinta-feira, 19 de dezembro de 2013 22h12min24s UTC-3, Luciano escreveu:

Dear all,

I am looking to consecutively sum the numbers in rows of a column with thousands of numbers until their sum is an exact expected value. Thus if the value of the sum is higher than that expected, the macro must start the sumin the following row and look for the expected value again. The macro mustlook for all situation in the column for which the sum is the searched value. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cells "A:A" and thatthe detected expected values could be listed in "B:B", for example, as following small example:



Searched sum: 23



5.0

18.0 23

12.0

11.0 23

6.0

1.0

11.0

24.0

11.0

6.0

6.0 23

12.0

6.0

5.0 23

12.0 23

18.0

6.0

6.0

5.0



Thanks in advance,

Luciano

Thank you for your help Claus!
However, when I tried your suggestion, the calculation was very slow and itshowed some unexpected problem. I think I explained wrong. I would need that the the numbers should be rounded to the units instead only consider their units. I was searching for the sum 81 and it give me the following results:

5.02087666
17.99563667
12.02066085
10.99112575
5.96302592
1.00180961
11.01358363
24.03497235
10.96953797
6.00187452
5.99388277
12.00290619
6.03242194
4.99056813 81.0
11.98501044
17.98239338
6.04408966 82.0
5.97139765
5.02337310
6.99745237
4.96712257
6.01464377
5.99510561
6.00791816 82.0
11.99775616
11.00304842
6.02804795
11.96389297 82.0