Increment numbering: But in the order they are inserted

FredSmith99 · #1 10-19-2015, 04:59 AM

Entering "data sets" into my document I need to ID them: I want this ID to be incremented "MaximumExistingID + 1" every time I insert a new data set. The key is that I do not want already inserted ID's to change (e.g. if I insert a new ID in the very beginning of the document).

All fields and settings I have tried, keep changing the numbers, incrementing them from the beginning to the end of the document. Any hint is welcome. Thanks!

Charles Kenyon · #2 10-19-2015, 05:21 AM

This cannot be done with a field, automatic numbering is sequential by design; vba would be required.

In Excel I accomplished this by a cell at the bottom of the column that has the ID numbers which says MAX of the column +1. I then use a macro to insert a new row above the current one and put the value found in the bookmark for the formula in the ID column for the new row.

Here is the vba code to assign the new number to column Q of the new row:

Code:

'   Insert new row above selection
'
    
    Dim lRow As Long
    lRow = Selection.Row
    Selection.EntireRow.Insert
'
'   Assign Case Number
'
    Dim lNext As Long
    
    lNext = Range("Next_Number").Value
    
    Cells(lRow, "Q").Value = lNext

The cell with the MAX formula has the label "Next_Number."

I prefer using Excel for data rather than Word and wouldn't know how to get the MAX function to work in Word. I'm sure that something could be done using vba.

FredSmith99 · #3 10-19-2015, 06:53 AM

Charles:
thanks for the feedback. Any suggestion what the VBA-code could look like in Word?
Thanks,
Fred

macropod · #4 10-19-2015, 08:20 PM

For this to work in Word, you'd need to store the last-used number in a document property or variable for retrieval. The code to do this might look like:

Code:

Sub InsertNextID()
Dim ID As Long, i As Long, bFnd As Boolean
Const strID As String = "MaxID"
bFnd = False
On Error Resume Next
With ActiveDocument
  For i = 1 To .Variables.Count
    If .Variables(i).Name = strID Then
      bFnd = True
      ID = .Variables(strID).Value + 1
      Variables(strID).Value = ID
      Exit For
    End If
  Next
  If bFnd = False Then
    ID = InputBox(Prompt:="Please input the First ID #", _
      Title:="ID Creation", Default:=1)
    .Variables.Add Name:=strID, Value:=ID
  End If
  Selection.Text = ID
End With
ErrExit:
End Sub

And the code you'll inevitably need to reset that ID might look like:

Code:

Sub IDReset()
Dim ID As Long, i As Long
Const strID As String = "MaxID"
On Error Resume Next
With ActiveDocument
  For i = 1 To .Variables.Count
    If .Variables(i).Name = strID Then
      ID = .Variables(strID).Value
      ID = InputBox(Prompt:="Please input the Reset ID #," & vbCr & _
        "equal to the last valid ID #", Title:="ID Creation", Default:=ID)
      Variables(strID).Value = ID
      Exit For
    End If
  Next
End With
End Sub

FredSmith99 · #5 10-19-2015, 10:45 PM

Hi Paul,
thank you for your code. It looks very good and has similarities to the way I have finally chosen (we worked in parallel): My data are identified by a prefix "D#", so
- in a first loop I determine the maximum ID that is available in the document,
- then those data that do not yet have an ID are assigned with one in Loop 2
Thanks for the feedback,
Fred
Here my approach:

Code:

 
Sub AddDataID()
NumberOfData = 0
DataMaxID = 0
AllDataHaveID = True
With Selection 'Look for D# in the document and determine the maximum ID that was already assigned
    .HomeKey Unit:=wdStory
    With .Find
        .Text = "D#??????"
        .Forward = True
        .MatchWildcards = True
    End With
    Do While .Find.Execute 'Loop 1: Determine the maximum ID
        NumberOfData = NumberOfData + 1
        FirstSpace = InStr(1, Selection, " ") 'Per definition there is a " " after the ID
        DataNumberFound = Val(Mid(Selection, 3, FirstSpace - 3))
        If DataNumberFound = 0 Then AllDataHaveID = False 'Not all requirements have an ID yet
        If DataNumberFound > DataMaxID Then DataMaxID = DataNumberFound
    Loop
End With
'Then loop 2 to assign those data that do not have an ID yet an ID starting from DataMaxID
End Sub

macropod · #6 10-19-2015, 11:00 PM

Your initial post described IDs being incremented every time you insert a new data set, but without regard to any existing sequencing. That's what the code I supplied provides for.

Your code, however, envisages searching through the document for all existing IDs (which could be a slow process in a long document) to find the highest one (which might not be the last one allocated if that one has since been deleted), then (apparently) you intend to sequentially add successive IDs to data presently lacking them.

The two approaches are quite different and yours doesn't really meet your "every time I insert a new data set" criterion.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Loop - Row increment	jrt	Excel Programming	1	04-16-2015 01:46 PM
Increment Numbers	Darbsp	Word	3	03-10-2015 05:25 AM
List Style Numbering picks up out of order number from LATER list	spthomas	Word	12	12-16-2013 05:23 PM
Word Heading Bullet Numbering increment	SASE1984	Word	1	02-10-2012 01:06 PM
Increment Numbers Help	Jazz43	Word	2	07-30-2010 02:48 AM