+ Reply to Thread
Results 1 to 11 of 11

Autofill 1A 2A 3A and So On

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    28

    Autofill 1A 2A 3A and So On

    Hello guys!

    How to make autofill in a series of 1A 2A 3A and so on in one column?
    Since if we drag down "1A 2A 3A" it will copy the same content as the above and not make a fill in a series?

    Thank you before!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Autofill 1A 2A 3A and So On

    use formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    then copy and paste values once you are done
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,971

    Re: Autofill 1A 2A 3A and So On

    One way, in A1:
    =Row() & "A"
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    10-01-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    28

    Re: Autofill 1A 2A 3A and So On

    Thank you guys!
    However my figures are more complex than that. I have to fill FEM15S-852B, FEM15S-853B, FEM15S-854B and so on in a series and it is not related to the row they are located.
    I again attach a sheet with the updated figures.
    Thanks!
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Autofill 1A 2A 3A and So On

    try this then
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-01-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    28

    Re: Autofill 1A 2A 3A and So On

    Quote Originally Posted by humdingaling View Post
    try this then
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    big thanks humdingaling.

    however, do you have a formula that not related to the row of the content?
    since by using that formula, I can't copy paste it directly to other cells with different row position.
    example: if I want to move "FEM15S-852B" which originally in the row 1 to row 2 by using ="FEM15S-"&851+ROW()&"B", then I have to change the formula to ="FEM15S-"&850+ROW()&"B" first.

    I intend to share the sheet containing this formula with my partners. I hope for a formula that could be shared easily. :D thanks!

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Autofill 1A 2A 3A and So On

    If your string in A1 always follow the same pattern i.e. few letters followed by a hyphen followed by a number followed by a single letter, I would do it like this instead of hard coding the string in the formula.....
    Assuming A1 contains FEM15S-852B, then

    In A2
    Please Login or Register  to view this content.
    and copy down.

    This way you can place any string in A1 with the same pattern.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Autofill 1A 2A 3A and So On

    with any formula solution.....if you don't copy & paste values after doing it....the formula may or may not have the correct context as it needs to be in reference to something

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: Autofill 1A 2A 3A and So On

    Quote Originally Posted by uckycool View Post
    big thanks humdingaling.
    however, do you have a formula that not related to the row of the content?
    The ROW function is not really linked to any cell content, its is ONLY being used as a counter - it is not even looking at any cell.

    To demonstrate this, put =ROW() in A1...it will show 1
    copy it down, and it will show 2, 3, 4 etc.

    Because your numbering starts at 851, humding is taking that value, and adding it to the row(). Perhaps a slightly different approach would have been to use =ROW(A1), but this can break down if you insert rows
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Registered User
    Join Date
    10-01-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    28

    Re: Autofill 1A 2A 3A and So On

    Thanks guys for helping me a lot. But I would use the sktneer formula since I got it just what I want.
    Huge thanks guys! :D

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: Autofill 1A 2A 3A and So On

    Happy to help, thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Autofill base on Active Cell and Autofill by row count of another sheet
    By enyak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2014, 10:06 PM
  2. [SOLVED] Very small AutoFill macro showing "AutoFill methode of range class failed" why ?
    By nur2544 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2013, 10:21 PM
  3. Macro for Autofill removes header info when there is no data to autofill
    By esturan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-06-2012, 01:42 PM
  4. Replies: 1
    Last Post: 06-17-2005, 04:05 PM
  5. Replies: 0
    Last Post: 03-02-2005, 12:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1