Macro to fill in subsequent data

[raghu2013]

raghu

Got a doubt:
Excel spreadsheet, the rows are:

1-62
1-63
Blank cell
1-75
1-76
Blank cell

My requirement is: wherever there is a Blank cell, it should be populated with the previous cell number +1
and placed within brackets.

For example the first Blank cell should become (1-64)
The next Blank cell should become (1-77)

Like this, there are hundreds of rows.
This should happen automatically through a script/formula/macro.

Need urgent help.

 

[raghu2013]

Re: raghu

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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

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 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.

 

[kevatarvind]

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

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

 

[kevatarvind]

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 ()

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

 

[ArthriticPanda]

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?

Excel Workbook
	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
Sheet1

AP

 

[kevatarvind]

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 ?

 

[texasalynn]

Re: raghu

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

 

[ArthriticPanda]

Re: raghu

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

 

[raghu2013]

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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"></colgroup><tbody>
</tbody>

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.

 

[raghu2013]

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

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.

 

[raghu2013]

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

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