Thread
:
Can excel find a sequence of numbers from a known total?
View Single Post
#
13
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
Sandy Mann
external usenet poster
Posts: 2,345
Can excel find a sequence of numbers from a known total?
Thanks for the link Dave - Chip was too modest to include it. <g
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
with @tiscali.co.uk
"Dave Peterson" wrote in message
...
Chip has some notes written by Alan Beban:
http://www.cpearson.com/excel/cells.htm
Sandy Mann wrote:
Hi Chip,
May I ask what type of *offset* you are using in your code - or point me
to
somewhere that I can look it up for myself.
What I mean is that your:
Set StartRng = StartRng(2, 1)
looks like it equivelant to:
Set StartRng = StartRng.Offset(rowoffset:=1, columnoffset:=0)
or Set StartRng = StartRng.Offset(1, 0)
What really surprises me is that
Set StartRng = StartRng(0, 0)
seems to be the StartRng minus one row and minus one column which is
definitely non-intuitive, (at least not the way that my mind works)
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
with @tiscali.co.uk
"Chip Pearson" wrote in message
...
Eric,
It was an interesting little diversion from the usual questions in the
newsgroup. It was fun to write. I put the solution up on my web site,
on a
page describing how to fill missing entries in a series of numbers. The
two problems are only tangentially related, but I couldn't think of any
other relevant page on this on which to include it.
http://www.cpearson.com/excel/Insert....htm#SumSeries
.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"efandango" wrote in message
...
Hello Chip,
That sir, is fantastic!. it did for me in the blink of an eye, what I
have
previously spent an eye-straining excessive amount of time on...
thank you
much appreciated.
regards
Eric
"Chip Pearson" wrote:
If I understand your question properly, you want find a contiguous
range
of
cells that adds up to a particular number, 68 in your example. The
following
code will search column A looking for a series of contiguous cells
that
add
up to the value in cell B1. It will scan until either the answer is
found or
a blank cell is encountered in column A, in which there is no series
that
adds up to Answer.
Sub FindSeries()
Dim StartRng As Range
Dim EndRng As Range
Dim Answer As Long
Dim TestTotal As Long
Answer = Range("B1") '<<< CHANGE
Set StartRng = Range("A1")
Set EndRng = StartRng
Do Until False
TestTotal = Application.Sum(Range(StartRng, EndRng))
If TestTotal = Answer Then
Range(StartRng, EndRng).Select
Exit Do
ElseIf TestTotal Answer Then
Set StartRng = StartRng(2, 1)
Set EndRng = StartRng
Else
Set EndRng = EndRng(2, 1)
If EndRng.Value = vbNullString Then
MsgBox "No series found"
Exit Do
End If
End If
Loop
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"efandango" wrote in message
...
Can excel find (and highlight) a continuous sequence of numbers
using
a
known
total as reference from the following list of numbers?
the answer is 68.
8
6
3
2
6
10
9
4
12
8
6
1
8
10
8
14
10
9
12
12
14
6
4
3
4
4
4
0
6
10
4
9
6
3
11
12
10
7
12
8
8
--
Dave Peterson
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann