How to add sequential numbers between values, within a range.
Category: [Excel] Demo Available
How to add sequential numbers between values, within a range
Requirement:
I have a named Range called "SEQ" in Column C that spans about 4000 rows.
Within this Range are various markers. "x"
New rows are routinely added.
I'm in need of a VBA script that will add a numerical sequence between these markers, in this named Range.
-- from this:
C
x
x
x
x
---to this.
C
x
1
2
3
x
1
x
1
2
3
4
x
Solution:
Code:
Sub test()
Dim x, i As Long, iCnt As Integer
With [SEQ]
x = .Value
For i = 1 To UBound(x, 1) - 1
If x(i, 1) = "x" Then
i = i + 1: iCnt = 0
Do Until x(i, 1) = "x"
iCnt = iCnt + 1
x(i, 1) = iCnt
i = i + 1
If i > UBound(x, 1) Then Exit For
Loop
End If
i = i - 1
Next
.Value = x
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to paste a cell value to the active cell |
| How to paste value when creating a master summary sheet |
| How to use looping to delete cells of similar value |
| How to create repeated cell values |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.