Alphanumeric fill problem!

[michetta]

Okay, I've been trying to think of a way to auto fill this series:

Column A

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>14.25.A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>14.25.B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>14.25.C</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>14.26.A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>14.26.B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>14.26.C</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>14.27.A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>14.27.B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>14.27.C</TD></TR></TBODY></TABLE>

I want to extend the series all the way to 14.200.C

Is there a formula or macro to help me do this?

Many thanks for any assistance!
Michelle

 

[AdamL]

Probably a better way out there, but if your first value is in A1, then in A2:

=IF(RIGHT(A1,1)="C","14."&MID(SUBSTITUTE(A1,"."," ",2),4,3)+1&".A",REPLACE(A1,LEN(A1),1,CHAR(CODE(RIGHT(A1,1))+1)))

filled down.

 

[jindon]

Without VBA/Formula

1) enter 14.25.A 14.25.B 14.25.C in A1:A3
2) select A1:A3
then filldown as you want

vba

Sub test()
With Range("a1:a3")
    .Value = [{"a1.25.A";"14.25.B";"14.25.C"}]
    .AutoFill .Resize((200 - 14) * 3)
End With
End Sub

 

[michetta]

Thank you AdamL - you are a miracle worker!!!!
M

 

[Peter_SSs]

You could also try this:

1. In any row 1 cell put this formula:
="14."&25+INT((ROW()-1)/3)&"."&CHAR(65+MOD(ROW()-1,3))

2. Copy down as far as required.

3. I would suggest then a Copy and Paste Special|Values in case rows are susequently added/deleted.

Excel Workbook
	A
1	14.25.A
2	14.25.B
3	14.25.C
4	14.26.A
5	14.26.B
6	14.26.C
7	14.27.A
8	14.27.B
9	14.27.C
10	14.28.A
Fill Series

 

[AdamL]

Hi jindon

For my benefit, would you be able to explain this, when you have a chance:

Without VBA/Formula

1) enter 14.25.A 14.25.B 14.25.C in A1:A3
2) select A1:A3
then filldown as you want

Cheers
A