"Insert" Macro
 

I am looking for a way to insert rows underneath a row, with a macro reading
a cell, and then inserting that number of rows underneath.

e.g. A column reads 500-505. The macro will seperate the numbers (500,505)
into adjecent cells, calculate the difference (5), and then insert that
number of columns underneath the original row (reading "500-505" with 5 blank
rows underneath). I figured out how to do the calculation, but I can't find
a way to insert the columns. Is there a way to do this? I already know the
answer is "yes", but is it feasable for a beginner macro'er to try and
implement?

Thanks,
Sean H

"Insert" Macro
 

try
Sub insertcalculatedrows()
'broken down so you can see what happens
On Error Resume Next
For Each c In Selection
x = InStr(c, "-")
y = Right(c, Len(c) - x)
z = Left(c, Len(c) - x)
c.Offset(1).Resize(y - z, 1).EntireRow.Insert
Next
End Sub

--
Don Guillett
SalesAid Software

"Sean H" wrote in message
...
I am looking for a way to insert rows underneath a row, with a macro
reading
a cell, and then inserting that number of rows underneath.

e.g. A column reads 500-505. The macro will seperate the numbers
(500,505)
into adjecent cells, calculate the difference (5), and then insert that
number of columns underneath the original row (reading "500-505" with 5
blank
rows underneath). I figured out how to do the calculation, but I can't
find
a way to insert the columns. Is there a way to do this? I already know
the
answer is "yes", but is it feasable for a beginner macro'er to try and
implement?

Thanks,
Sean H

"Insert" Macro
 

Sean

try one of these:

Sub InsertRows()
' all variables defined
' step by step approach
Dim CellContent As String
Dim FirstNumber As Long
Dim SecondNumber As Long
Dim Difference As Long
Dim BreakPoint As Long
On Error GoTo NoHyphen
CellContent = ActiveCell.Value
BreakPoint = WorksheetFunction.Find("-", CellContent)
FirstNumber = --Left(CellContent, BreakPoint - 1)
SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
Difference = SecondNumber - FirstNumber
ActiveCell.Offset(1, 0).Resize(Difference).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Sub InsertRowsShortVersion()
' no defined variables
On Error GoTo NoHyphen
ActiveCell.Offset(1, 0).Resize( _
--Right( _
ActiveCell.Value, _
Len(ActiveCell.Value) - _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value)) - _
--Left( _
ActiveCell.Value, _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value) - 1) _
).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Regards

Trevor


"Sean H" wrote in message
...
I am looking for a way to insert rows underneath a row, with a macro
reading
a cell, and then inserting that number of rows underneath.

e.g. A column reads 500-505. The macro will seperate the numbers
(500,505)
into adjecent cells, calculate the difference (5), and then insert that
number of columns underneath the original row (reading "500-505" with 5
blank
rows underneath). I figured out how to do the calculation, but I can't
find
a way to insert the columns. Is there a way to do this? I already know
the
answer is "yes", but is it feasable for a beginner macro'er to try and
implement?

Thanks,
Sean H

"Insert" Macro
 

Trevor,

Thanks a lot, that worked great. Here is another problem of mine. Is there
a way to loop this so that I can execute the macro once, and have it expand
my whole file? After that, the macro needs to fill in the numbers between
(and including) the numbers. My file looks something like this:

505-520
521-530
532-540

As you will notcie, sometimes the range jumps a number and leaves it out,
eliminating my option for a simple x = x + 1 style loop.

Thanks again for all your help.

Regards,
Sean Heckathorne

"Trevor Shuttleworth" wrote:

Sean

try one of these:

Sub InsertRows()
' all variables defined
' step by step approach
Dim CellContent As String
Dim FirstNumber As Long
Dim SecondNumber As Long
Dim Difference As Long
Dim BreakPoint As Long
On Error GoTo NoHyphen
CellContent = ActiveCell.Value
BreakPoint = WorksheetFunction.Find("-", CellContent)
FirstNumber = --Left(CellContent, BreakPoint - 1)
SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
Difference = SecondNumber - FirstNumber
ActiveCell.Offset(1, 0).Resize(Difference).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Sub InsertRowsShortVersion()
' no defined variables
On Error GoTo NoHyphen
ActiveCell.Offset(1, 0).Resize( _
--Right( _
ActiveCell.Value, _
Len(ActiveCell.Value) - _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value)) - _
--Left( _
ActiveCell.Value, _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value) - 1) _
).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Regards

Trevor


"Sean H" wrote in message
...
I am looking for a way to insert rows underneath a row, with a macro
reading
a cell, and then inserting that number of rows underneath.

e.g. A column reads 500-505. The macro will seperate the numbers
(500,505)
into adjecent cells, calculate the difference (5), and then insert that
number of columns underneath the original row (reading "500-505" with 5
blank
rows underneath). I figured out how to do the calculation, but I can't
find
a way to insert the columns. Is there a way to do this? I already know
the
answer is "yes", but is it feasable for a beginner macro'er to try and
implement?

Thanks,
Sean H

"Insert" Macro
 

Also,

Is there a way to save this macro so that I can use it every time I open a
new workbook, until I decide to delete the macro?

Thanks,
Sean Heckathorne

"Trevor Shuttleworth" wrote:

Sean

try one of these:

Sub InsertRows()
' all variables defined
' step by step approach
Dim CellContent As String
Dim FirstNumber As Long
Dim SecondNumber As Long
Dim Difference As Long
Dim BreakPoint As Long
On Error GoTo NoHyphen
CellContent = ActiveCell.Value
BreakPoint = WorksheetFunction.Find("-", CellContent)
FirstNumber = --Left(CellContent, BreakPoint - 1)
SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
Difference = SecondNumber - FirstNumber
ActiveCell.Offset(1, 0).Resize(Difference).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Sub InsertRowsShortVersion()
' no defined variables
On Error GoTo NoHyphen
ActiveCell.Offset(1, 0).Resize( _
--Right( _
ActiveCell.Value, _
Len(ActiveCell.Value) - _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value)) - _
--Left( _
ActiveCell.Value, _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value) - 1) _
).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Regards

Trevor


"Sean H" wrote in message
...
I am looking for a way to insert rows underneath a row, with a macro
reading
a cell, and then inserting that number of rows underneath.

e.g. A column reads 500-505. The macro will seperate the numbers
(500,505)
into adjecent cells, calculate the difference (5), and then insert that
number of columns underneath the original row (reading "500-505" with 5
blank
rows underneath). I figured out how to do the calculation, but I can't
find
a way to insert the columns. Is there a way to do this? I already know
the
answer is "yes", but is it feasable for a beginner macro'er to try and
implement?

Thanks,
Sean H

"Insert" Macro
 

You could put it in a workbook and store that workbook in your XLStart folder.

Many people who want to have this kind of macro available each time they open
excel will name that file personal.xls.



Sean H wrote:

Also,

Is there a way to save this macro so that I can use it every time I open a
new workbook, until I decide to delete the macro?

Thanks,
Sean Heckathorne

"Trevor Shuttleworth" wrote:

Sean

try one of these:

Sub InsertRows()
' all variables defined
' step by step approach
Dim CellContent As String
Dim FirstNumber As Long
Dim SecondNumber As Long
Dim Difference As Long
Dim BreakPoint As Long
On Error GoTo NoHyphen
CellContent = ActiveCell.Value
BreakPoint = WorksheetFunction.Find("-", CellContent)
FirstNumber = --Left(CellContent, BreakPoint - 1)
SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
Difference = SecondNumber - FirstNumber
ActiveCell.Offset(1, 0).Resize(Difference).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Sub InsertRowsShortVersion()
' no defined variables
On Error GoTo NoHyphen
ActiveCell.Offset(1, 0).Resize( _
--Right( _
ActiveCell.Value, _
Len(ActiveCell.Value) - _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value)) - _
--Left( _
ActiveCell.Value, _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value) - 1) _
).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Regards

Trevor


"Sean H" wrote in message
...
I am looking for a way to insert rows underneath a row, with a macro
reading
a cell, and then inserting that number of rows underneath.

e.g. A column reads 500-505. The macro will seperate the numbers
(500,505)
into adjecent cells, calculate the difference (5), and then insert that
number of columns underneath the original row (reading "500-505" with 5
blank
rows underneath). I figured out how to do the calculation, but I can't
find
a way to insert the columns. Is there a way to do this? I already know
the
answer is "yes", but is it feasable for a beginner macro'er to try and
implement?

Thanks,
Sean H




--

Dave Peterson

"Insert" Macro
 

Sean

this will loop through a selection and create the blank lines.

Sub InsertRowsLoop()
' all variables defined
' step by step approach
Dim SelectedColumn As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCount As Long
Dim CellContent As String
Dim FirstNumber As Long
Dim SecondNumber As Long
Dim Difference As Long
Dim BreakPoint As Long

SelectedColumn = Selection.Column
FirstRow = Selection.Row
LastRow = Selection.Row + Selection.Rows.Count - 1

On Error GoTo NoHyphen
' Note: work up from the bottom
For iCount = LastRow To FirstRow Step -1
CellContent = Cells(iCount, SelectedColumn).Value
BreakPoint = WorksheetFunction.Find("-", CellContent)
FirstNumber = --Left(CellContent, BreakPoint - 1)
SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
Difference = SecondNumber - FirstNumber
Cells(iCount, SelectedColumn).Offset(1,
0).Resize(Difference).EntireRow.Insert
Next 'iCount
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

I'll leave you to increment the numbers. All you need to do is work down
from the top and, if the cell is blank, set it to the previous cell plus 1.
No doubt there are other ways using AutoFill, etc.

Regards

Trevor


"Sean H" wrote in message
...
Trevor,

Thanks a lot, that worked great. Here is another problem of mine. Is
there
a way to loop this so that I can execute the macro once, and have it
expand
my whole file? After that, the macro needs to fill in the numbers between
(and including) the numbers. My file looks something like this:

505-520
521-530
532-540

As you will notcie, sometimes the range jumps a number and leaves it out,
eliminating my option for a simple x = x + 1 style loop.

Thanks again for all your help.

Regards,
Sean Heckathorne

"Trevor Shuttleworth" wrote:

Sean

try one of these:

Sub InsertRows()
' all variables defined
' step by step approach
Dim CellContent As String
Dim FirstNumber As Long
Dim SecondNumber As Long
Dim Difference As Long
Dim BreakPoint As Long
On Error GoTo NoHyphen
CellContent = ActiveCell.Value
BreakPoint = WorksheetFunction.Find("-", CellContent)
FirstNumber = --Left(CellContent, BreakPoint - 1)
SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
Difference = SecondNumber - FirstNumber
ActiveCell.Offset(1, 0).Resize(Difference).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Sub InsertRowsShortVersion()
' no defined variables
On Error GoTo NoHyphen
ActiveCell.Offset(1, 0).Resize( _
--Right( _
ActiveCell.Value, _
Len(ActiveCell.Value) - _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value)) - _
--Left( _
ActiveCell.Value, _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value) - 1) _
).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Regards

Trevor


"Sean H" wrote in message
...
I am looking for a way to insert rows underneath a row, with a macro
reading
a cell, and then inserting that number of rows underneath.

e.g. A column reads 500-505. The macro will seperate the numbers
(500,505)
into adjecent cells, calculate the difference (5), and then insert that
number of columns underneath the original row (reading "500-505" with 5
blank
rows underneath). I figured out how to do the calculation, but I can't
find
a way to insert the columns. Is there a way to do this? I already
know
the
answer is "yes", but is it feasable for a beginner macro'er to try and
implement?

Thanks,
Sean H