Dave,
Thank you for responding and for your code! I incorporated your ideas
along with Habeeb's (above) and my own ideas and came up with the code
below. If you see any room for improvement, please let me know, as I
am still very new to this coding.
Sub fillInTheRows()
Sheets("Raw").Select
Range("A1").Select
Dim Count As Integer
Count = 100
Dim rowCount As Integer
rowCount = 1
Dim offs As Integer
offs = 1
upperLimit = 4999
Application.ScreenUpdating = False
'Check to see if the data is there...
If Range("A1") = "" Then 'If the first cell is empty, kill program
MsgBox "The first cell is empty... please enter raw data"
End 'if it is the case that the first cell is empty, exit macro
End If
'Since the data is there, insert rows where needed...
If Range("A1") = 100 Then 'When the first cell is 100, we are
ready to begin.
Do While (offs + 100) < upperLimit
If ActiveCell.Offset(1).Value = (Count + offs) Then 'If
the next cell is one more than the previous cell, then..
ActiveCell.Offset(1).Select 'go to the next cell in
the column
offs = offs + 1 'increase the offset from the top
Else 'if the numbers are not in order...
ActiveCell.Offset(1).Select
Selection.EntireRow.insert
ActiveCell.FormulaR1C1 = 100 + offs 'put the
appropriate number in the new row
offs = offs + 1 'increase the offset value by 1
'MsgBox offs 'print the offset number for verification
End If
Loop
End If
End Sub
On Apr 21, 6:20 am, Dave Bonallack <[email protected]> wrote:
> Hi Dustin,
> Paste the following code into a module.
>
> Sub InsertMissing()
>
> Do Until ActiveCell.Value = ""
> A = ActiveCell.Value
> B = ActiveCell.Offset(1, 0).Value
> If B - A > 1 Then
> ActiveCell.Offset(1, 0).EntireRow.Select
> Selection.Insert Shift:=xlDown
> ActiveCell.Value = ActiveCell.Offset(-1, 0) + 1
> Else: ActiveCell.Offset(1, 0).Select
> End If
> Loop
>
> End Sub
>
> Before running, save your workbook in case something "unexpected" happens.
> You have to select the first numbered cell in your list, then run the code.
> Hope this helps.
> Regards - Dave.
>
>
>
> > Date: Mon, 20 Apr 2009 07:57:06 -0700
> > Subject: $$Excel-Macros$$ Need Help with Macro, Insert Rows based on
> > another cell's value
> > From: [email protected]
> > To: [email protected]
>
> > Hello! I am new to this group, and new to VBA in general, so I would
> > appreciate any insight / explanations into this =)
>
> > Here is the situation:
>
> > There will be raw data in the form of a spreadsheet which is in
> > chronological order, though not in steps of 1. So for instance, Column
> > A could be numbered 1,2,3,4,5,15,16,45 ...etc. There will also be
> > corresponding data in columns following it. The following is an
> > example of what it could look like (Columns A and B, though there will
> > be many more columns)
>
> > 100 this is 100
> > 101 this is 101
> > 102 this is 102
> > 103 this is 103
> > 104 this is 104
> > 110 this is 110
> > ...
>
> > What I need this macro to do, is to insert a row where the order is
> > not in steps of 1, and then copy the appropriate number into that new
> > row. Thus:
>
> > 100 this is 100
> > 101 this is 101
> > 102 this is 102
> > 103 this is 103
> > 104 this is 104
> > 105
> > 106
> > 107
> > 108
> > 109
> > 110 this is 110
> > ...
>
> > I would super appreciate help with this!
>
> > Thanks!!
>
> > Dustin
>
> _________________________________________________________________
> Need a new place to rent, share or buy? Let ninemsn property search for
> you.http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Eco...
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
If you find any spam message in the group, please send an email to:
Ayush Jain @ [email protected] or
Ashish Jain @ [email protected]
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
