How can you number one cell value in a range and then start the numbering over when the value of the cell changes

user_18884196 0
Viewed 41

I have a range that can have multiple values in the range but the values can repeat. I have tried to write a macro that will do this and I hit a road block. I can number the cells in the entire range from 1 to whatever the count is at the end of the range. here is the code I have so far and a list of what the result looks like. The second list is what I what the end result to be.

Sub Count_Items_Then_Repeat_On_Data_Change()

Dim rng As Range

Dim cell As Range

Dim count As Long

count = 1

Set rng = Application.Selection

For Each cell In rng

cell.Offset(0, 0).Value = cell.Value & "-" & count

count = count + 1


Next cell


End Sub
Wrong Output Correct Output
Column A Column A
1 1.01-1 1.01-1
2 1.01-2 1.01-2
3 1.01-3 1.01-3
4 1.01-4 1.01-4
5 1.02-5 1.02-1
6 1.02-6 1.02-2
7 1.02-7 1.02-3
8 1.02-8 1.02-4
vba excel
1 Answers

I'm not entirely sure I understand your question based on the data and what it seems you're trying to do.

It looks like you want the count to reset when the original value of the cell changes.

Provided the entire selection is in order, you can simply test the next cell before changes to see if the count should reset like so:

Sub Count_Items_Then_Repeat_On_Data_Change()
    Dim rng As Range
    Dim cell As Range
    Dim count As Long
    count = 1
    Set rng = Application.Selection
    For Each cell In rng
        'If the next cell has a different value, reset count after we change this one
        If cell.Value <> cell.Offset(1, 0).Value Then
            cell.Value = cell.Value & "-" & count
            count = 1 'Reset count to 1
        Else
            cell.Value = cell.Value & "-" & count
            count = count + 1
        End If
    Next cell
End Sub

I also went ahead and changed cell.Offset(0,0).Value - you don't need the .Offset(0,0) - it does nothing; you can just use cell.Value.

Results:

Results

user_12748200
Related