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.

 

[kevatarvind]

Re: raghu

hi the code working fine in my file but if you are facing problem then pls upload your file in any sharing site sample data where facing problem and paste link here

 

[raghu2013]

Re: raghu

hi the code working fine in my file but if you are facing problem then pls upload your file in any sharing site sample data where facing problem and paste link here

Hi,
Thanks immensely for the coding; that was brilliant. i shall do some troubleshooting as to why in one particular case, it does not work. It is working for all cases: only one number: i shall test on my own.

Also, where and how can i upload my sample excel file please?

Three small matters are hanging fire.
1) i want opening and closing parenthesis for the numbers which replace the blanks. for example, if 1-70 replaces the previously blank cell, it should read (1-70)

2) I want the blank cells to be highlighted in a color, say, yellow: VBA script coding for this is requested, so that i may insert this before your coding, so that the blank cells are highlighted and then they are filled in: i may know as to what are the cells that have been newly inserted: for validation purposes.

3) The user opening the VBA script and changing the end value every time is somewhat odd, and i feel that can be done, or rather, should be done, programmatically. just like checking end of file condition in databases.
Can we have a check for range from one to Lastrow (variable), checking for condition as:

if lastrow+next row cell =blank cell and lastrow+next to next cell=blank cell, then set the range as from 1 to lastrow.

this will obviate the necessity of changing the end variable every time, for different ranges, and will enable me to run the script as a shortkey enabled macro.

Thanks in anticipation.

Raghu

 

[kevatarvind]

Re: raghu

hi try below code assuming your data in col a and it will highlight filled cell color to Yellow and will find the last row itself in col A
and for the opening and closing parenthesis you need to upload your file any sharing site with your actual data then easy to create a code

you can upload your file any sharing site and just paste the link here

like DropBox , Mediafire 4shared there is lots of site where you can upload your file

Sub Fill_Subsequent()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Range("A1").Select
Do Until ActiveCell.Row = lr
    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
    ActiveCell.Interior.ColorIndex = 6
    End If
    ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub

 

[raghu2013]

Re: raghu

hi try below code assuming your data in col a and it will highlight filled cell color to Yellow and will find the last row itself in col A
and for the opening and closing parenthesis you need to upload your file any sharing site with your actual data then easy to create a code

you can upload your file any sharing site and just paste the link here

like DropBox , Mediafire 4shared there is lots of site where you can upload your file

Sub Fill_Subsequent()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Range("A1").Select
Do Until ActiveCell.Row = lr
    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
    ActiveCell.Interior.ColorIndex = 6
    End If
    ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub

Hi,
thanks for the leads.

Shall do. also shall try the code given by you.

 

[raghu2013]

Re: raghu

Hi,
thanks for the leads.

Shall do. also shall try the code given by you.

one hundred percent success. very very happy. you, sir are a genius. my colleague will be very happy. i am going to set this up as a short key for macro. the earlier three hours for him has been reduced to 5 seconds. thanks a lot.

Raghu

 

[kevatarvind]

Re: raghu

Thanks For The Feedback

Your Welcome

 

[raghu2013]

Re: raghu

Thanks For The Feedback

Your Welcome

Hi,
this is the code that you sent me:

Sub Fill_Blanks()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Range("A1").Select
Do Until ActiveCell.Row = lr
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
ActiveCell.Interior.ColorIndex = 6
End If
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub


It works fine, no issues.
now, one extension is required by the user. the above code works perfectly fine.
However, if there is a cell which says 1-74.1 and the next cell is blank, then the blank cell should be filled with

1-74.1-1. Can the above code be modified to add this condition also? Thanks and regards, Raghu

<tbody>
</tbody>