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

Akash030193

New Member
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
1Revision
20
31
42
5
60
71
82
9
100
111
122
13
140
151
162
17

<tbody>
</tbody>

After applying macro
A
1Revision
20
31
42
53
60
71
82
93
100
111
122
133
140
151
162
173

<tbody>
</tbody>


2) Second Example

Question

A
10
20
30
4
50
60
7

<tbody>
</tbody>


After applying macro

A
10
20
30
41
50
60
71

<tbody>
</tbody>


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
 

Osvaldo Palmeiro

Well-known Member
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
 

Rick Rothstein

MrExcel MVP
You can also do it directly without adding and then replacing formulas...
Code:
[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]
 

Akash030193

New Member
Thanks Rick,

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

Regards,
Akash Patel
 

Akash030193

New Member
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
1R0
2R1
3
4R0
5R1
6
7R0
8
9R0
10R0
11

<tbody>
</tbody>
Answer
A
1R0
2R1
3R2
4R0
5R1
6R2
7R0
8R1
9R0
10R0
11R1

<tbody>
</tbody>
 

Rick Rothstein

MrExcel MVP
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
 

Akash030193

New Member
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
1Apple
2Banana
3
4Grapes
5
6
7Carrot

<tbody>
</tbody>

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

<tbody>
</tbody>
 

Rick Rothstein

MrExcel MVP
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:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top