Macro to customize AutoFill (2003/2007)

Topic

New Reply

When you select cells with two or more consequent numbers and drag it, AutoFill will fill the selection with next numbers in sequence. For example, after ABC-2 and ABC-3 will be ABC-4, ABC-5, etc.
But I have a column with characters after the numbers: 100-2009, 101-2009, 102-2009, etc.

How to write the macro to read the last cell in the column before the selection (say, 105-2009) and fill the selection with consequent numbers: 106-2009, 107-2009, etc (all numbers are in the same column) ?

Thank you in advance,

Reply | Quote

Replies

Try this:

Sub FillCustom()
Dim strVal As String
Dim intSeq As Integer
Dim strChar As String
Dim i As Integer
strVal = Selection.Cells(0).Value
intSeq = Val(strVal)
strChar = Mid(strVal, InStr(strVal, "-"))
For i = 1 To Selection.Count
intSeq = intSeq + 1
Selection.Cells(i) = intSeq & strChar
Next i
End Sub

Reply | Quote

It’s exactly what I wanted. Thank you.

Reply | Quote

Hi Hans
I believe that your code will fail its intended purpose if the selection contains more than one column, or the result can be converted to a date. The following is suggested as an alternative.

Option Explicit
Public Sub LeadNoFillDown()
Dim StNo As String
Dim Suff As String
Dim rng As Range
Dim Ctr As Long

Set rng = Range(Cells(Selection.Row, _
Selection.Column).Address, _
Cells(Selection.Row + Selection.Rows.Count - 1, _
Selection.Column).Address)
With rng
Suff = .Cells(1)
Suff = Right(Suff, Len(Suff) - InStr(1, Suff, "-") + 1)
StNo = Left(.Cells(1), Len(.Cells(1)) - Len(Suff))
For Ctr = 2 To .Cells.Count
StNo = Format(Val(StNo) + 1, "000")
.Cells(Ctr) = StNo & Suff
Next Ctr
End With
End Sub

Reply | Quote

I interpreted kaplinb’s question to mean that cells in a single column would be selected, so I didn’t take a multi-column selection into account.

Set rng = Range(Cells(Selection.Row, _
Selection.Column).Address, _
Cells(Selection.Row + Selection.Rows.Count – 1, _
Selection.Column).Address)

is a rather roundabout way to specify

Set rng = Selection.Columns(1)

On the other hand, I didn’t assume that the left hand part would be at most 3 digits, as you appear to assume in Format(Val(StNo) + 1, “000”)

Reply | Quote

[indent]

---

roundabout way to specify
Set rng = Selection.Columns(1)

---

[/indent]
I could not agree more.

katlimb can revise the line StNo = Format(Val(StNo) + 1, “000”) to provide the maximum number of characters necessary. I like to force the number of characters in cases like this to allow for sorting. I was pleased to see that the code did not fail when the left hand portion exceeded 999; I had not considered that when coding.

Reply | Quote