Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 27

Macro to fill in subsequent data

This is a discussion on Macro to fill in subsequent data within the Excel Questions forums, part of the Question Forums category; Originally Posted by texasalynn Excel 2010 A B 1 1-62 1-62 2 1-63 1-63 3 Blank cell 1-64 4 1-75 ...

  1. #11
    New Member
    Join Date
    Jun 2013
    Posts
    14

    Default Re: raghu

    Quote Originally Posted by texasalynn View Post
    Excel 2010
    A B
    1 1-62 1-62
    2 1-63 1-63
    3 Blank cell 1-64
    4 1-75 1-75
    5 1-76 1-76
    6 Blank cell 1-77
    Sheet3

    Worksheet Formulas
    Cell Formula
    B3 =LEFT(B2,FIND("-",B2))&RIGHT(B2,LEN(B2)-FIND("-",B2))+1
    B6 =LEFT(B5,FIND("-",B5))&RIGHT(B5,LEN(B5)-FIND("-",B5))+1



    then you can filter that column for blanks and copy the formula to those cells
    Hi,
    thanks for the reply.
    the formula worked when i converted the text to number format. but, actually, i wanted something more. this only served the purpose partially.
    1) The newly copied entry should be within parenthesis, i.e., it should read (1-30.2)
    2) The subsequent numbering works fine when the previous number is 1-29: the new number becomes 1-30.
    But, when the previous number is 1-30.1, then i want the new number to be 1-30.2 and not 1-31.1. Could you check on this please?
    3) Lastly, the formula has to work in one shot on all the blank cells: any workaround on this please?

    thanks for your help.

  2. #12
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,030

    Default Re: raghu

    Hi And Welcome to The Board
    Try Below VB Code The Below Code will cover Range A1 To A15 if you want to change the range for e.g if you want to change it to A1 To A500 then just change the highlighted row no 500
    Code:
    Sub Fill_Subsequent()
    Application.ScreenUpdating = False
    Range("A1").Select
    Do Until ActiveCell.Row = 15
        If ActiveCell.Value = "" Then
        ActiveCell.NumberFormat = "General"
        ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH(""."",R[-1]C)),LEFT(R[-1]C,SEARCH(""-"",R[-1]C))& MID(R[-1]C,SEARCH(""-"",R[-1]C)+1,99)*1+1,LEFT(R[-1]C,SEARCH(""."",R[-1]C))&MID(R[-1]C,SEARCH(""."",R[-1]C)+1,99)*1+1)"
        ActiveCell.Value = ActiveCell.Value
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
    Application.ScreenUpdating = True
    End Sub
    Excel 2010 / Windows 7 (Home)
    Excel 2007 / windows xp (Work)


  3. #13
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,030

    Default Re: raghu

    hi i have read you need filled no. like this ( 1-30 ) so try below one but note that
    if continues Blank cell will found more then one blank cell then it will fill no only one blank cell next blank cell will give error and above one will work any blank cell found that will not give any error but will not put any ()
    Code:
    Sub Fill_Subsequent()
    Application.ScreenUpdating = False
    Range("A1").Select
    On Error Resume Next
    Do Until ActiveCell.Row = 25
        If ActiveCell.Value = "" Then
        ActiveCell.NumberFormat = "General"
        ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH(""."",R[-1]C)),LEFT(R[-1]C,SEARCH(""-"",R[-1]C))& MID(R[-1]C,SEARCH(""-"",R[-1]C)+1,99)*1+1,LEFT(R[-1]C,SEARCH(""."",R[-1]C))&MID(R[-1]C,SEARCH(""."",R[-1]C)+1,99)*1+1)"
        ActiveCell.Value = "(" & ActiveCell.Value & ")"
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
    Application.ScreenUpdating = True
    End Sub
    Last edited by kevatarvind; Jun 13th, 2013 at 11:57 AM.
    Excel 2010 / Windows 7 (Home)
    Excel 2007 / windows xp (Work)


  4. #14
    Board Regular ArthriticPanda's Avatar
    Join Date
    Feb 2013
    Location
    Scotland
    Posts
    267

    Default Re: Macro to fill in subsequent data

    Yes, I am aware of the concept you described. The reason I asked about the parentheses was to establish if they were an important requirement... a macro can be written to use autofill to give you what you need but the parentheses (as kevatarvind has pointed out) make it more of a challenge.

    With that in mind, would the following be of use?

    Sheet1

     A
    11-73
    21-74
    3*1-75
    41-76
    51-77
    61-78
    7*1-79
    81-80
    91-81
    101-82
    11*1-83
    12*1-84
    131-85
    141-86
    151-87.21
    16*1-87.22
    171-89
    181-90
    19*1-91
    201-92
    211-93


    Excel tables to the web >> Excel Jeanie HTML 4

    AP
    Excel 2003/2007/2010

    - Try Excel jeanie or MrExcel HTML Maker to show your examples in posts.
    - If posting VBA code, use Code Tags or the 'Go Advanced' button when replying to a post.

    IMPORTANT: 'always make a backup copy before trying new code - avoid losing all that work!'



  5. #15
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,030

    Default Re: Macro to fill in subsequent data

    Quote Originally Posted by ArthriticPanda View Post
    Yes, I am aware of the concept you described. The reason I asked about the parentheses was to establish if they were an important requirement... a macro can be written to use autofill to give you what you need but the parentheses (as kevatarvind has pointed out) make it more of a challenge.
    AP
    you are absolutely right or another way if they parentheses just to know that those cell were blank then we can highlight those cell with different color ?
    Excel 2010 / Windows 7 (Home)
    Excel 2007 / windows xp (Work)


  6. #16
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,308

    Default Re: raghu

    Quote Originally Posted by raghu2013 View Post
    No, this does not work. firstly, why did you add another column B? this is not what i asked for.
    very simple. there is only one column A. if i copy the above formula, with suitable modifications, the formula
    just gets copied to the cell. no sorry, this did not work.
    Only used a second column to show the results, wasn't intended to be your final results. I'm going to bow out

  7. #17
    Board Regular ArthriticPanda's Avatar
    Join Date
    Feb 2013
    Location
    Scotland
    Posts
    267

    Default Re: raghu

    Quote Originally Posted by texasalynn View Post
    Only used a second column to show the results, wasn't intended to be your final results. I'm going to bow out
    I thought from the original post that this was an urgent request from the OP? Clearly not as he hasn't responded to any of the 3 of us. I'm going to follow you and bow out too.

    AP
    Excel 2003/2007/2010

    - Try Excel jeanie or MrExcel HTML Maker to show your examples in posts.
    - If posting VBA code, use Code Tags or the 'Go Advanced' button when replying to a post.

    IMPORTANT: 'always make a backup copy before trying new code - avoid losing all that work!'



  8. #18
    New Member
    Join Date
    Jun 2013
    Posts
    14

    Default Re: Macro to fill in subsequent data

    Quote Originally Posted by ArthriticPanda View Post
    Yes, I am aware of the concept you described. The reason I asked about the parentheses was to establish if they were an important requirement... a macro can be written to use autofill to give you what you need but the parentheses (as kevatarvind has pointed out) make it more of a challenge.

    With that in mind, would the following be of use?

    Sheet1

    * A
    1 1-73
    2 1-74
    3 *1-75
    4 1-76
    5 1-77
    6 1-78
    7 *1-79
    8 1-80
    9 1-81
    10 1-82
    11 *1-83
    12 *1-84
    13 1-85
    14 1-86
    15 1-87.21
    16 *1-87.22
    17 1-89
    18 1-90
    19 *1-91
    20 1-92
    21 1-93


    Excel tables to the web >> Excel Jeanie HTML 4

    AP
    Thanks for the reply. The reason for being unable to reply was that this is my first time, and i did not notice page 2 of the post and thought that no one had replied to me. my apologies.. my heartfelt thanks for your help. for first timers to forums like this, it is somewhat of a maze and unfamiliar. all this while, i was thinking that i am still awaiting a reply. thans a million for your help. that really helped. the formula that was given.

  9. #19
    New Member
    Join Date
    Jun 2013
    Posts
    14

    Default Re: raghu

    Quote Originally Posted by kevatarvind View Post
    Hi And Welcome to The Board
    Try Below VB Code The Below Code will cover Range A1 To A15 if you want to change the range for e.g if you want to change it to A1 To A500 then just change the highlighted row no 500
    Code:
    Sub Fill_Subsequent()
    Application.ScreenUpdating = False
    Range("A1").Select
    Do Until ActiveCell.Row = 15
        If ActiveCell.Value = "" Then
        ActiveCell.NumberFormat = "General"
        ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH(""."",R[-1]C)),LEFT(R[-1]C,SEARCH(""-"",R[-1]C))& MID(R[-1]C,SEARCH(""-"",R[-1]C)+1,99)*1+1,LEFT(R[-1]C,SEARCH(""."",R[-1]C))&MID(R[-1]C,SEARCH(""."",R[-1]C)+1,99)*1+1)"
        ActiveCell.Value = ActiveCell.Value
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
    Application.ScreenUpdating = True
    End Sub

    Thanks for your reply. i shall try out the quote applicable for more than one cell. i have many blank cells in my requirement. shall try it out pronto. thanks a lot for your reply. if there is any delay in my reply, it is because of the fact that i am new to forums like these and i did not realise that there is page 2 also for the messages. thanks again.

  10. #20
    New Member
    Join Date
    Jun 2013
    Posts
    14

    Default Re: raghu

    Quote Originally Posted by kevatarvind View Post
    Hi And Welcome to The Board
    Try Below VB Code The Below Code will cover Range A1 To A15 if you want to change the range for e.g if you want to change it to A1 To A500 then just change the highlighted row no 500
    Code:
    Sub Fill_Subsequent()
    Application.ScreenUpdating = False
    Range("A1").Select
    Do Until ActiveCell.Row = 15
        If ActiveCell.Value = "" Then
        ActiveCell.NumberFormat = "General"
        ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH(""."",R[-1]C)),LEFT(R[-1]C,SEARCH(""-"",R[-1]C))& MID(R[-1]C,SEARCH(""-"",R[-1]C)+1,99)*1+1,LEFT(R[-1]C,SEARCH(""."",R[-1]C))&MID(R[-1]C,SEARCH(""."",R[-1]C)+1,99)*1+1)"
        ActiveCell.Value = ActiveCell.Value
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
    Application.ScreenUpdating = True
    End Sub
    Hi,
    thanks that worked. actually, the user wants the opening and closing parenthesis: as he wnts to distinguish the previously blank pages, with parenthesis. also , there was an unexpected result: the next number to 1-69 should become 1-70: but the cell got filled in with Jan-70: this is definitely an error: and i do not how this error has come. any workarounds please?

    Thanks.
    raghu

Page 2 of 3 FirstFirst 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com