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
Range("A1").Offset(i,0).select
If Left(.selection.Formula, 4) = "seq(" Then
activecell.value = "seq(" & Seq_Number & ")"
Seq_Number = Seq_Number + 1
Else
End If
Next i

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)
seq=parameter
End function

That should do it.

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

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

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

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
already.



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
Do
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

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.

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.

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.

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.

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

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!