Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Add next sequence number in next blank row cell only..Excel VBA program

  1. #1
    New Member
    Join Date
    Apr 2019
    Location
    Ahmedabad
    Posts
    22
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Post Add next sequence number in next blank row cell only..Excel VBA program

    Hello! I need your help in below query.

    I am looking for a VBA program that help me to add next sequence number in next blanks row cell only (one cell only). For example, 5th row is blank cell, i need to place next sequence number as "3". Similarly in the same column, refer row 9, i need to place next sequence number as "3"... similarly for all subsequent series..

    1) Question
    A
    1 Revision
    2 0
    3 1
    4 2
    5
    6 0
    7 1
    8 2
    9
    10 0
    11 1
    12 2
    13
    14 0
    15 1
    16 2
    17

    After applying macro
    A
    1 Revision
    2 0
    3 1
    4 2
    5 3
    6 0
    7 1
    8 2
    9 3
    10 0
    11 1
    12 2
    13 3
    14 0
    15 1
    16 2
    17 3


    2) Second Example

    Question

    A
    1 0
    2 0
    3 0
    4
    5 0
    6 0
    7


    After applying macro

    A
    1 0
    2 0
    3 0
    4 1
    5 0
    6 0
    7 1


    Code to be run if there is any next blank row in the column "A", and to be run for one next cell only.



    https://1drv.ms/x/s!AgoBOBbQ2MyJgQcKF83--PVdg_3j



    ****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">A1Revision2031425
    6071829
    10011112213
    14015116217

  2. #2
    Board Regular
    Join Date
    Feb 2009
    Posts
    550
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add next sequence number in next blank row cell only..Excel VBA program

    Hi. Try:
    Code:
    Sub AddNextInBlanks()
     With Range("A2:A" & Cells(Rows.Count, 1).End(3).Row + 1)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C+1"
      .Value = .Value
     End With
    End Sub
    Osvaldo

  3. #3
    New Member
    Join Date
    Apr 2019
    Location
    Ahmedabad
    Posts
    22
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Post Re: Add next sequence number in next blank row cell only..Excel VBA program

    Thanks Osvaldo Palmeiro,

    It is working. Thank you once again. I am grateful to you......


    Regards,
    Akash Patel

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,224
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Add next sequence number in next blank row cell only..Excel VBA program

    You can also do it directly without adding and then replacing formulas...
    Code:
    Sub AddNextInBlanks() With Range("A2:A" & Cells(Rows.Count, 1).End(3).Row + 1) .Value = Evaluate("IF(" & .Address & "=""""," & .Offset(-1).Address & "+1," & .Address & ")") End With End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member
    Join Date
    Apr 2019
    Location
    Ahmedabad
    Posts
    22
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add next sequence number in next blank row cell only..Excel VBA program

    Thanks Rick,

    This program is also working well. Thank you once again for your support. I am grateful to you......


    Regards,
    Akash Patel

  6. #6
    New Member
    Join Date
    Apr 2019
    Location
    Ahmedabad
    Posts
    22
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Add next sequence number in next blank row cell only..Excel VBA program

    In continuation to thread message, can you please provide macro program as per below table?

    I want to add next sequence number in below table with prefix "R". If my cell value is R1, then after in next first blank cell it needs to be R2...vice verse.....
    Question
    A
    1 R0
    2 R1
    3
    4 R0
    5 R1
    6
    7 R0
    8
    9 R0
    10 R0
    11
    Answer
    A
    1 R0
    2 R1
    3 R2
    4 R0
    5 R1
    6 R2
    7 R0
    8 R1
    9 R0
    10 R0
    11 R1

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,224
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Add next sequence number in next blank row cell only..Excel VBA program

    Quote Originally Posted by Akash030193 View Post
    In continuation to thread message, can you please provide macro program as per below table?

    I want to add next sequence number in below table with prefix "R". If my cell value is R1, then after in next first blank cell it needs to be R2...vice verse.....
    Give this macro a try...
    Code:
    Sub FillBlankWithOneMoreThanPreviousRnumber()
      Dim Ar As Range
      For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
        Ar(Ar.Count).Offset(1).Value = "R" & Mid(Ar(Ar.Count), 2) + 1
      Next
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    New Member
    Join Date
    Apr 2019
    Location
    Ahmedabad
    Posts
    22
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Add next sequence number in next blank row cell only..Excel VBA program

    Thanks Rick,

    It is working. Thank you again!

  9. #9
    New Member
    Join Date
    Apr 2019
    Location
    Ahmedabad
    Posts
    22
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Post Re: Add next sequence number in next blank row cell only..Excel VBA program

    Rick,

    Can you share simple macro program in which I can fill blank cell with some text. For example,

    Where there is blank cell in column A, I can fill blank cell with Text as per below tables.

    A
    1 Apple
    2 Banana
    3
    4 Grapes
    5
    6
    7 Carrot

    A
    1 Apple
    2 Banana
    3 Not allocated
    4 Grapes
    5 Not allocated
    6 Not allocated
    7 Carrot

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,224
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Add next sequence number in next blank row cell only..Excel VBA program

    Quote Originally Posted by Akash030193 View Post
    Rick,

    Can you share simple macro program in which I can fill blank cell with some text. For example,

    Where there is blank cell in column A, I can fill blank cell with Text as per below tables.
    Assuming the blanks are real blanks (not formulas displaying ""), give this macro a try...
    Code:
    Sub NotAllocated()
      Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlBlanks) = "Not allocated"
    End Sub
    Last edited by Rick Rothstein; May 25th, 2019 at 10:02 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

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
  •