# 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. ## Re: raghu

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-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. ## 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```

3. ## 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```

4. ## 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 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

5. ## Re: Macro to fill in subsequent data

Originally Posted by 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.
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 ?

6. ## Re: raghu

Originally Posted by raghu2013
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. ## Re: raghu

Originally Posted by texasalynn
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

8. ## Re: Macro to fill in subsequent data

Originally Posted by 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?

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. ## Re: raghu

Originally Posted by kevatarvind
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. ## Re: raghu

Originally Posted by kevatarvind
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 First 123 Last

#### Posting Permissions

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