Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    2,328
    Thanks
    0
    Thanked 1 Time in 1 Post

    Macro to customize AutoFill (2003/2007)

    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,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to customize AutoFill (2003/2007)

    Try this:
    <code>
    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</code>

  3. #3
    Silver Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    2,328
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to customize AutoFill (2003/2007)

    It's exactly what I wanted. Thank you.

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to customize AutoFill (2003/2007)

    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.
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Option</font color=blue> <font color=blue>Explicit</font color=blue>
    <font color=blue>Public</font color=blue> <font color=blue>Sub</font color=blue> LeadNoFillDown()
    <font color=blue>Dim</font color=blue> StNo <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>
    <font color=blue>Dim</font color=blue> Suff <font color=blue>As</font color=blue> String
    <font color=blue>Dim</font color=blue> rng <font color=blue>As</font color=blue> Range
    Dim Ctr As <font color=blue>Long</font color=blue>

    <font color=blue>Set</font color=blue> rng = Range(Cells(Selection.Row, _
    Selection.Column).Address, _
    Cells(Selection.Row + Selection.Rows.Count - 1, _
    Selection.Column).Address)
    <font color=blue>With</font color=blue> rng
    Suff = .Cells(1)
    Suff = Right(Suff, Len(Suff) - InStr(1, Suff, "-") + 1)
    StNo = Left(.Cells(1), Len(.Cells(1)) - Len(Suff))
    <font color=blue>For</font color=blue> Ctr = 2 <font color=blue>To</font color=blue> .Cells.Count
    StNo = Format(Val(StNo) + 1, "000")
    .Cells(Ctr) = StNo & Suff
    <font color=blue>Next</font color=blue> Ctr
    <font color=blue>End</font color=blue> <font color=blue>With</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue></font color=black></code></div hiblock>
    Regards
    Don

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to customize AutoFill (2003/2007)

    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")

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to customize AutoFill (2003/2007)

    <hr>roundabout way to specify
    Set rng = Selection.Columns(1)<hr>
    I could not agree more. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    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.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •