MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Sequence field

  Asked By: Diane    Date: Nov 09    Category: MS Office    Views: 1212

Has anyone constructed a function for Excel which works like the SEQ field in

What I want to do is to be able to insert a function in a column and the first
instance return 1, the second instance (in order down the column) return 2,

I need to be able to insert new instances in the column between existing ones
(and delete instances) and have them pick their correct number according to
their positioning and then the increase ripple down through all subsequent
instances. i.e. if I inserted one somewhere between 7 and 8 it would become
8, 8 would become 9, etc.

The column might have other stuff in it.

The distances between instances would be irregular

I would accept the instances always being in the same column.

I tried using a public variable. Resetting the first one to 0 in the recalc
event and using a function as follows:

Function NextNo()
x = x + 1
NextNo = x
End Function

Unfortunately the recalc works sporadically all over the page so 1 appears at
the lower right and the other numbers are more or less in the reverse order to
what you expect. If you insert an instance it gets the highest number +1 _and
then keeps it on a recalc_.

I also tried Function seq (Shown below)

This has the cell address in quotes as the parameter (except the first
instance which has "Start")

This works for the first instance but not for the others. It comes up with

I am finding debugging it very difficult. I set a breakpoint on

"If Left(.Range(.Cells(myRow, rngCell.Column)).Formula, 4) = "seq(" Then"

And it broke. However when I press f8 it goes out of debug mode.

I am not so certain I have the use of Range and Cells right. Or even that I am
using a sensible algorithm in the first place.

Function seq(strSource As String) As Integer

If strSource = "start" Then
seq = 1
Exit Function
End If
Dim rngCell As Range
Dim rngPrevCell
Dim myRow
Dim intSeqNo As Integer

Set rngCell = Range(strSource)
For myRow = rngCell.Row - 1 To 0 Step -1
With ActiveSheet
If Left(.Range(.Cells(myRow, rngCell.Column)).Formula, 4) = "seq(" Then
rngPrevCell = .Cells(myRow.rngCell.Column)
End If
End With

intSeqNo = rngPrevCell.Value
intSeqNo = intSeqNo + 1
seq = intSeqNo

End Function



10 Answers Found

Answer #1    Answered By: Harriet Ward     Answered On: Nov 09

1) Strange seeing you asking questions around here

2) Whilst your algorithm  appears logical, I did note the following:

Your FOR NEXT statement starts at the end and works  backwards to zero.
Why don't you start  at row  1, and work down to the last row. That way,
you can ensure that you always start at 1, and then when you get to the
next "SEQ(", you can chronologically add 1 to the result.

My pseudo-code:

Dim Number_Of_Rows as Integer
Dim i as Integer
Dim Seq_Number as Integer

' Determine number  of rows containing data
Number_Of_Rows = Range(Selection, Selection.End(xlDown)).Select

Seq_Number = 1

For i = 1 to Number_Of_Rows
If Left(.selection.Formula, 4) = "seq(" Then
activecell.value = "seq(" & Seq_Number & ")"
Seq_Number = Seq_Number + 1
End If
Next i

Answer #2    Answered By: Darwishi Massri     Answered On: Nov 09

I think if the cell  value is "seq(" & Seq_Number & ")" it will display all
that. I just want to display a number  (or later a letter buts lets not worry
about that). That is why I used a function. The way you have it, if
seq_number is 7 it will display "seq(7)". I just want it to display 7.

I cannot just overwrite it with 7 because then I will never find it again and
it would not update if another Seq was inserted  above it.

However, I might be able to combine the two.

If I use your method to make the formula  =seq(7),

And then have a function  which goes

Function seq(Parameter)
End function

That should do it.

Now you know why I asked the question. I got valuable information.

Answer #3    Answered By: Binge Fischer     Answered On: Nov 09

I ended up with a sub driven by a command button and the function  as follows

We can't use the worksheet.change event because it is fired every time the sub
changes one of the fields so it gets in a loop.

The function is in a module and the sub is in the worksheet code (although for
practical purposes I will be moving it to a module).

Private Sub RecalcSeq()
Dim i As Integer
Dim Number_Of_Rows As Integer
Dim Seq_Number As Integer

' Determine number  of rows containing data
Number_Of_Rows = UsedRange.Rows.Count

Seq_Number = 1
For i = 1 To Number_Of_Rows
If Left(Range("D" & i).Formula, 5) = "=seq(" Then
Range("D" & i).Formula = "=seq(" & Seq_Number & ")"
Seq_Number = Seq_Number + 1
End If
Next i

End Sub

Function seq(intSeqNo As Integer) As Integer
seq = intSeqNo
End Function

Answer #4    Answered By: Allison Stanley     Answered On: Nov 09

Slight change - highlighted in Bold. Happens when you test code only
after sending it...

Answer #5    Answered By: Xander Thompson     Answered On: Nov 09

re:"I would accept  the instances  always being in the same column."

I had a go to allow instances in multiple columns and came up with the
vba below.

A few points, at this late stage:
1.I've used Long instead of Integer as I often got an overflow error.

2.I had a problem when the first line(s) of the spreadsheet had not
been used, so changed it as Paul suggested to
Number_Of_Rows = Range(Selection, Selection.End(xlDown)).Count
but got a perceptible delay sometimes as it ran through all the rows
on the sheet, dependent on which cell  was selected before running, so
despite knowing that xlCellTypeLastCell can on occasion be
idiosyncratic, I used that instead.

3.It renumbers the sequence  regardless of which columns they're in,
and gives sequence numbers  on the same row  the same number  (hope
that's how you wanted it!).

4.It might be wise to give the function  a more obcsure name since I've
used Find which looks for '=seq(' anywhere in the cell, not just at
the left, so it's just conceivable that it could be found as part of
text elsewhere.
5.I've not tested as rigourously as I usually do, since it's just a
suggestion - you seem to have the problem solved to your satisfaction

Private Sub RecalcSeq()
Dim i As Long
Dim Number_Of_Rows As Long
Dim Seq_Number As Long
' Determine number of rows containing data
Number_Of_Rows = Selection.SpecialCells(xlCellTypeLastCell).Row
Seq_Number = 0
For i = 1 To Number_Of_Rows
With Rows(i)
Set c = .Find(What:="=seq(", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Seq_Number = Seq_Number + 1
c.Formula = "=seq(" & Seq_Number & ")"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next i
End Sub

Answer #6    Answered By: Nathan Evans     Answered On: Nov 09

This was only the start  of this project and I will be developing it further.
Your suggestions will all be part of the solution, I suspect.

My ultimate goal is to be able to have:

- Several separate sequences intermingled
- sub-sequences
- the ability to use Arabic (ordinary) numbers, roman UC, roman LC, alpha UC,
alpha LC
- to restart sequences
- to run sequences across many sheets or a whole workbook.

I have in my mind how to do this but I needed a firm foundation for this.

Answer #7    Answered By: Laurie Lawrence     Answered On: Nov 09

Consider using the row  () function. You can always subtract a constant from
the result to get it to start  at 1 in the row you want.

Answer #8    Answered By: Madaniyah Malik     Answered On: Nov 09

The issue with that was that I just want these numbers  every now and again so
they would not relate to rows. I want to end up with 1 in D5, then 2 in D17,
then 3 in D44 - then I want to put the function  in D23 and that takes the
value 3 and D44 now becomes 4.

Like I want to do another sequence  across seven sheets and have that
automatically update. I think that will be a fairly easy extension of what I
have done.

Answer #9    Answered By: Essie Garza     Answered On: Nov 09

I see. I didn't keep Paul's reply, so apologies if I'm parroting him,
but ...

If column  D will be empty unless it has the sequence  number in it, then you
can use an anchored "max" call. E.g. my active range  is D2 to Dnnn. In D8
I have =MAX(D$2:D7)+1. D2:D7 are empty. Result: 1. Copy to D19, where it
reads: =MAX(D$2:D18)+1. Result: 2. Copy to D35, where it reads:
=MAX(D$2:D34)+1. Result: 3.

Copy it to D13, where it reads: =MAX(D$2:D12)+1. Result: 2. D19 now 3; D35
now 4.

Answer #10    Answered By: Eleanor Hunt     Answered On: Nov 09

I cannot avoid the possibility that users (about 400 belonging to 125
different organisations) will put something in the relevant column  (even if I

I have used your solution in the past and it works  well.

See my next posting to Pascal if you are interested in following this saga!

Didn't find what you were looking for? Find more on Sequence field Or get search suggestion and latest updates.