Not even sure where to start with this

[logandiana]

I've used VBA before for something similar, but that was when I had some helper tables. Not sure where to begin with this one.
Basically for each range, I need to assign a sequential number to the range, and then I need to assign a sequential number to each row within the range.

Here's what I've got:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And here's what I need using VBA:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]4
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]4
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD]6
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

You can do what you want with formula. Assuming your data starts in cell A1, 1 in cell B1 and C1 and then put these formulas in the indicated cells and copy them down to the end of your data...

B2: =IF(A1=A2,B1,B1+1)

C2: =IF(A1=A2,C1+1,1)

 

[Rick Rothstein]

You can do what you want with formula. Assuming your data starts in cell A1, 1 in cell B1 and C1 and then put these formulas in the indicated cells and copy them down to the end of your data...

B2: =IF(A1=A2,B1,B1+1)

C2: =IF(A1=A2,C1+1,1)

If you must have a VBA solution, then give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub SpecialNumbering()
  Dim R As Long, LastRow As Long
  Application.ScreenUpdating = False
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:C1").Value = 1
  Range("B2:B" & LastRow).Formula = "=IF(A1=A2,B1,B1+1)"
  Range("C2:C" & LastRow).Formula = "=IF(A1=A2,C1+1,1)"
  Range("B2:C" & LastRow).Value = Range("B2:C" & LastRow).Value
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[logandiana]

I had been thinking of a completely different approach, but this simple formula did what I needed.
Then I incorporated it into the VBA almost identically to what you have before I even refreshed to see your second post!
Thanks for the help