true autnumbering

[childofthe1980s]

Hello:

Column A has the inventory item number TV in cells A1 through A3. Column B
has the letters A, B, and C in B1, B2, and B3 respectively.

Column A also has the inventory item number RADIO in cells A4 through A8.
Column B has the letters A, B, C, D, and E in B4, B5, B6, B7, and B8
respectively.

Is there a way to place, in column C, the numbers 1, 2, 3 in cells C1
through C3 and the numbers 1, 2, 3, 4, and 5 in cells C4 through C8?

What I want to do is place an increasing numbering sequence in column C
matching the "changes" in column B for each inventory item in column A.

childofthe1980s

 

[ryguy7272]

Does this work for you?

Sub x()

Dim rngL1 As Range
Dim rngL2 As Range
Dim rngA As Range
Dim rngB As Range
Dim rngOutA As Range
Dim rngOutB As Range

Set rngL1 = Range("A1", Range("A1").End(xlDown))
Set rngL2 = Range("B1", Range("B1").End(xlDown))

Set rngOutA = Range("C1")
Set rngOutB = Range("C" & (rngL1.Rows.Count * rngL2.Rows.Count * 2))
For Each rngA In rngL1.Cells
For Each rngB In rngL2.Cells
rngOutA = rngA.Value & rngB.Value
rngOutB = rngB.Value & rngA.Value
Set rngOutA = rngOutA.Offset(1, 0)
Set rngOutB = rngOutB.Offset(-1, 0)
Next
Next
End Sub

Source:
http://www.ozgrid.com/forum/showthread.php?t=59110


HTH,
Ryan---

 

[Gary''s Student]

If the codes in column B always recycle A,B,C...

In C1, enter 1
In C2, enter:

=IF(CODE(B2)-CODE(B1)=1,C1+1,1) and copy down. For example:

A 1
B 2
C 3
D 4
E 5
F 6
A 1
B 2
C 3
A 1
B 2
C 3