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

[Akash030193]

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
[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Revision[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

After applying macro
[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Revision[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


2) Second Example

Question

[TABLE="class: grid, width: 10, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


After applying macro

[TABLE="class: grid, width: 10, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Akash030193]

Thanks Rick for your prompt reply. It is working..?

 

[Akash030193]

Can you please advise following macro

Row 1 to 5 in column A is filled with some text.
Row 6 in same column is empty..
Row 7 to 10 in same column is filled with some text
Row 11 in same column is empty..vice versa

I want a macro that fill all blank column (6, 11, etc) with text “AAA”. Please help

 

[Akash030193]

Can you please advise following macro

Row 1 to 5 in column A is filled with some text.
Row 6 in same column is empty..
Row 7 to 10 in same column is filled with some text
Row 11 in same column is empty..vice versa

I want a macro that fill all blank column (6, 11, etc) with text “AAA”. Please help

Please help

 

[Akash030193]

In continuation with above macro, I want to update sequence number as A B C D....next blank row to be filled with next sequence number, if

For example,
[TABLE="class: grid, width: 10, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]ColumnA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]
Answer to be..
[TABLE="class: grid, width: 10, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]ColumnA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Can you please advise following macro

Row 1 to 5 in column A is filled with some text.
Row 6 in same column is empty..
Row 7 to 10 in same column is filled with some text
Row 11 in same column is empty..vice versa

I want a macro that fill all blank column (6, 11, etc) with text “AAA”. Please help

Will any blanks after the last filled cell need to have "AAA" placed in them? If so, what can be used to determine the last row that fills will need to go down to?

 

[Rick Rothstein]

In continuation with above macro, I want to update sequence number as A B C D....next blank row to be filled with next sequence number, if

For example,
[TABLE="class: grid, width: 10, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]ColumnA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]

Will the number of blanks between letters always only be one cell or could there be multiple blank cells between letters that need to be filled in?

 

[Akash030193]

Will the number of blanks between letters always only be one cell or could there be multiple blank cells between letters that need to be filled in?

Yes, number of blanks between letters always be one cell.

 

[Rick Rothstein]

Give this macro a try...

Sub FillBlanksWithNextSequenceLetter()
  Dim R As Long
  For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    If Cells(R, "A").Value = "" Then
      Cells(R, "A").Value = Chr(Asc(Cells(R - 1, "A").Value) + 1)
    End If
  Next
End Sub

 

[Akash030193]

Give this macro a try...

Sub FillBlanksWithNextSequenceLetter()
  Dim R As Long
  For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    If Cells(R, "A").Value = "" Then
      Cells(R, "A").Value = Chr(Asc(Cells(R - 1, "A").Value) + 1)
    End If
  Next
End Sub

thank you very very much
It’s working