I have the following numbers in column A

1

2

3

-2

-4

5

5

-10

3

3

-6

I need in column B next to each number - the number telling me to

which

group of sum zero it belows(by selecting first cell next to column A

up to last cell - (B1:B11) i.e

1,2,3,-2,-4 would each have number 1 next to each of them in column B

(first group of sum zero)

5,5,-10 would each have number 2 next each of them in column B (second

group of sum zero)

3,3,-6 would each have number 3 next to each of them in column B

(third group of sun zero)

Need a macro which would work in any range of numbers in any column

( not necessarily column A i.e if numbers are in column D - count

would be in column E next to the number

Pls help thxs

Here is one way. I made certain assumptions:

1. The numbers will always be contiguous by group. Each series of numbers

which must sum to zero are together from the start.

2. What happens if there are numbers which, pursuant to rule #1, do not

evemtially sum to zero? Omit them and keep processing for the duration.

Sub GroupSumZeroes()

Dim rng As Range, n As Long, ttl As Double, nIndex As Long

Dim cell1 As Range, cell2 As Range

Set rng = Range(Range("A1"), Range("A1").End(xlDown)) ' Adjust as needed

rng.Offset(, 1).ClearContents

nIndex = 1

Set cell1 = rng(1)

For n = 1 To rng.Cells.Count

ttl = ttl + rng(n).Value

If ttl = 0 Then

Set cell2 = rng(n)

Range(cell1, cell2).Offset(, 1).Value = nIndex

Set cell1 = cell2(2)

nIndex = nIndex + 1

End If

Next

End Sub

--

Tim Zych

http://www.yqcomputer.com/

Workbook Compare - Excel data comparison utility

Free and Pro versions

1. The numbers will always be contiguous by group. Each series of numbers

which must sum to zero are together from the start.

2. What happens if there are numbers which, pursuant to rule #1, do not

evemtially sum to zero? Omit them and keep processing for the duration.

Sub GroupSumZeroes()

Dim rng As Range, n As Long, ttl As Double, nIndex As Long

Dim cell1 As Range, cell2 As Range

Set rng = Range(Range("A1"), Range("A1").End(xlDown)) ' Adjust as needed

rng.Offset(, 1).ClearContents

nIndex = 1

Set cell1 = rng(1)

For n = 1 To rng.Cells.Count

ttl = ttl + rng(n).Value

If ttl = 0 Then

Set cell2 = rng(n)

Range(cell1, cell2).Offset(, 1).Value = nIndex

Set cell1 = cell2(2)

nIndex = nIndex + 1

End If

Next

End Sub

--

Tim Zych

http://www.yqcomputer.com/

Workbook Compare - Excel data comparison utility

Free and Pro versions

On Mar 17, 10:01 am, "Tim Zych" <tzych@nospam at earthlink dot net>

thxs tim - the macro works great - have adjusted it so that it can

work on all ranges other than starting A1 as per below - can you pls

modify the macro so as to add the letters "JE" before the resulting

count numbers e.g JE1, JE2, JE3, ....

thxs beforehand - what do you think of using an input message box

where the user can change JE to anything else later.

THXS THXS

thxs tim - the macro works great - have adjusted it so that it can

work on all ranges other than starting A1 as per below - can you pls

modify the macro so as to add the letters "JE" before the resulting

count numbers e.g JE1, JE2, JE3, ....

thxs beforehand - what do you think of using an input message box

where the user can change JE to anything else later.

THXS THXS

ub GroupSumZeroes()

Dim rng As Range, n As Long, ttl As Double, nIndex As Long

Dim cell1 As Range, cell2 As Range

Dim Prefix As String

Prefix = InputBox("Enter Prefix", , "JE")

If Prefix = "" Then Exit Sub

Set rng = Range(Range("A1"), Range("A1").End(xlDown)) ' Adjust as needed

rng.Offset(, 1).ClearContents

nIndex = 1

Set cell1 = rng(1)

For n = 1 To rng.Cells.Count

ttl = ttl + rng(n).Value

If ttl = 0 Then

Set cell2 = rng(n)

Range(cell1, cell2).Offset(, 1).Value = Prefix & nIndex

Set cell1 = cell2(2)

nIndex = nIndex + 1

End If

Next

End Sub

--

Tim Zych

http://www.higherdata.com

Workbook Compare - Excel data comparison utility

Free and Pro versions

"al" < XXXX@XXXXX.COM > wrote in message

news: XXXX@XXXXX.COM ...

Dim rng As Range, n As Long, ttl As Double, nIndex As Long

Dim cell1 As Range, cell2 As Range

Dim Prefix As String

Prefix = InputBox("Enter Prefix", , "JE")

If Prefix = "" Then Exit Sub

Set rng = Range(Range("A1"), Range("A1").End(xlDown)) ' Adjust as needed

rng.Offset(, 1).ClearContents

nIndex = 1

Set cell1 = rng(1)

For n = 1 To rng.Cells.Count

ttl = ttl + rng(n).Value

If ttl = 0 Then

Set cell2 = rng(n)

Range(cell1, cell2).Offset(, 1).Value = Prefix & nIndex

Set cell1 = cell2(2)

nIndex = nIndex + 1

End If

Next

End Sub

--

Tim Zych

http://www.higherdata.com

Workbook Compare - Excel data comparison utility

Free and Pro versions

"al" < XXXX@XXXXX.COM > wrote in message

news: XXXX@XXXXX.COM ...

n Mar 17, 10:31 am, "Tim Zych" <tzych@nospam at earthlink dot net>

wrote:

thxs thxs

i tried to add code below after your macro but it does not work - thxs

for your help!!!

Selection.Value = "JE" & Selection.Value

wrote:

thxs thxs

i tried to add code below after your macro but it does not work - thxs

for your help!!!

Selection.Value = "JE" & Selection.Value

n Mar 17, 10:31 am, "Tim Zych" <tzych@nospam at earthlink dot net>

wrote:

hi tim - need a last favor from you - if i don't want my series of

numbers to start with 1 i.e 1, 2, 3 but say starting with 12, 13, 14 -

how can i add an input box message for the stating number (would be

great to leave the default number as 1 but subject to change in the

input box) thxs

wrote:

hi tim - need a last favor from you - if i don't want my series of

numbers to start with 1 i.e 1, 2, 3 but say starting with 12, 13, 14 -

how can i add an input box message for the stating number (would be

great to leave the default number as 1 but subject to change in the

input box) thxs

1. total numbers of numbers input , largest number and smallest number and average of numbers

2. putting zeros to certain number of leading bits in a number in VB

3. putting zeros to certain number of leading bits in a number in

4. Word 2k Outline Numbering - Number format - Number style - Previous Level Number

5. equation number, table number, figure number linked to the same paragraph number - Wanted ideas

6. count each cell that have a number and take that number and count.

7. Number count - postcodes and need to count the numbers of tim...

8. how to generate two random numbers whose sum has a fixed value subjected to bounds

9. how to generate two random numbers whose sum

10. Zeros at the beginning of a number in a number field

11. Creating zero's in front of number and after number

12. word 2003 - numbering: caption numbers reset to zero

13. Number Field not showing zero's at the end of the number.

14. while entering credit card numbers last number changes to zero

15. Preceding a number by zeros, that is still a number

6 post • Page:**1** of **1**