Sequential Number macro

**rhudgins** · 07-15-2010, 04:55 PM

A59 is #1, A86 is #2 and A113 is #3. Can someone help me write a macro to automatically insert these #'s in sequential order. A59 will always be #1 and more #'s will be added over time.

Any help would be great!

**Leith Ross** · 07-15-2010, 05:06 PM

Hello rhudgins,

Are you sure you want a macro to do this? You easily use a formula to increment the number.

**shg** · 07-15-2010, 05:08 PM

Select A58:A84 and drag down as far as desired.

**rhudgins** · 07-15-2010, 05:23 PM

A macro would be great because the data is going to be deleted and inserted frequently.

**Leith Ross** · 07-15-2010, 05:32 PM

Hello rhudgins,

Here is the macro ...

Sub AddSequenceNumbers()

  Dim N As Long
  Dim Rng As Range
  Dim RngEnd As Range
  
    N = 1
    Set Rng = Range("A1")
    Set RngEnd = Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = Range(Rng, RngEnd)
    
      For Each Cell In Rng
        If Cell = "Trade #" Then Cell.Offset(1, 0) = N: N = N + 1
      Next Cell
      
End Sub