Habeeb,
Thank you for your response! I have taken the concepts in your code
and the code in Dave's post below and I have come up with the
following. If you have any suggestions or see things that I could do
better in it, please let me know as I am still getting the hang of
this coding =)
Thanks!
Dustin
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 20, 12:18 pm, "Habeeb Mohammed" <[email protected]> wrote:
> This could should work
>
> Sub insert()
> i = 1
>
> Do
> If Range("A" & i).Value = "" Then Exit Do
> If Range("A" & i + 1).Value - Range("A" & i).Value > 1 Then
> m = Range("A" & i + 1).Value - Range("A" & i).Value
> Range("A" & i + 1).Select
> l = i + 1
> For k = 1 To m - 1
> Range("A" & l).Select
> Selection.EntireRow.insert
> Range("A" & l).Value = Range("A" & (l - 1)).Value + 1
> l = l + 1
> Next k
>
> Else
>
> End If
> i = i + 1
>
> Loop
> End Sub
>
> -----Original Message-----
> From: [email protected] [mailto:[email protected]]
>
> On Behalf OfDustin
> Sent: 20 April 2009 22:57
> To: MS EXCEL AND VBA MACROS
> Subject: $$Excel-Macros$$ Need Help with Macro, Insert Rows based on another
> cell's value
>
> 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
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
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]
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
