formula for inserting rows

[Carol49]

Need formula that will allow a number e.g. 5 to be placed in a cell, which
t\will then insert 5 rows. Thanks

[Bob Phillips]

That needs VBA.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Resize(.Value).EntireRow.Insert
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

"Carol49" <[email protected]> wrote in message
news:[email protected]...
> Need formula that will allow a number e.g. 5 to be placed in a cell, which
> t\will then insert 5 rows. Thanks

[Carol49]

"Carol49" wrote:

> Need formula that will allow a number e.g. 5 to be placed in a cell, which
> t\will then insert 5 rows. Thanks
Formula is what I am looking for, but I am getting a syntax error in the line
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
I have copied and pasted, should I be doing something else? Thanks

[Bob Phillips]

Have you inserted it in a worksheet code module as instructed?

--
HTH

Bob Phillips

"Carol49" <[email protected]> wrote in message
news:[email protected]...
>
>
> "Carol49" wrote:
>
> > Need formula that will allow a number e.g. 5 to be placed in a cell,
which
> > t\will then insert 5 rows. Thanks
> Formula is what I am looking for, but I am getting a syntax error in the
line
> If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
> I have copied and pasted, should I be doing something else? Thanks

[Carol49]

"Bob Phillips" wrote:

> Have you inserted it in a worksheet code module as instructed?
>
> --
> HTH
>
> Bob Phillips
>
> "Carol49" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > "Carol49" wrote:
> >
> > > Need formula that will allow a number e.g. 5 to be placed in a cell,
> which
> > > t\will then insert 5 rows. Thanks
> > Formula is what I am looking for, but I am getting a syntax error in the
> line
> > If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
> > I have copied and pasted, should I be doing something else? Thanks
>
>
> Yes, I am right clicking on sheet tab and clicking on View Code and then pasting in formula. Thgat line always comes up red and when I go to enter in the appropriate cells on the correct sheet I gfet the Compile Error - Syntax Error. Thanks

[Bob Phillips]

I am confused, it is fine here.

You say you pasted the formula. What exactly have you copied in?

--
HTH

Bob Phillips

"Carol49" <[email protected]> wrote in message
news:[email protected]...
>
>
> "Bob Phillips" wrote:
>
> > Have you inserted it in a worksheet code module as instructed?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Carol49" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > >
> > > "Carol49" wrote:
> > >
> > > > Need formula that will allow a number e.g. 5 to be placed in a cell,
> > which
> > > > t\will then insert 5 rows. Thanks
> > > Formula is what I am looking for, but I am getting a syntax error in
the
> > line
> > > If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
> > > I have copied and pasted, should I be doing something else? Thanks
> >
> >
> > Yes, I am right clicking on sheet tab and clicking on View Code and then
pasting in formula. Thgat line always comes up red and when I go to enter
in the appropriate cells on the correct sheet I gfet the Compile Error -
Syntax Error. Thanks

[Carol49]

"Bob Phillips" wrote:

> I am confused, it is fine here.
>
> You say you pasted the formula. What exactly have you copied in?
>
> --
> HTH
>
> Bob Phillips
>
> "Carol49" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Have you inserted it in a worksheet code module as instructed?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Carol49" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > >
> > > > "Carol49" wrote:
> > > >
> > > > > Need formula that will allow a number e.g. 5 to be placed in a cell,
> > > which
> > > > > t\will then insert 5 rows. Thanks
> > > > Formula is what I am looking for, but I am getting a syntax error in
> the
> > > line
> > > > If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
> > > > I have copied and pasted, should I be doing something else? Thanks
> > >
> > >
> > > Yes, I am right clicking on sheet tab and clicking on View Code and then
> pasting in formula. Thgat line always comes up red and when I go to enter
> in the appropriate cells on the correct sheet I gfet the Compile Error -
> Syntax Error. Thanks
>
>
> I have tried from Private Worksheet.....
also from On Error Go....
and I stop after End Sub
Thanks

[Bob Phillips]

Can you ,ail me your workbook?

--
HTH

Bob Phillips

"Carol49" <[email protected]> wrote in message
news:[email protected]...
>
>
> "Bob Phillips" wrote:
>
> > I am confused, it is fine here.
> >
> > You say you pasted the formula. What exactly have you copied in?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Carol49" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Have you inserted it in a worksheet code module as instructed?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Carol49" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >
> > > > >
> > > > > "Carol49" wrote:
> > > > >
> > > > > > Need formula that will allow a number e.g. 5 to be placed in a
cell,
> > > > which
> > > > > > t\will then insert 5 rows. Thanks
> > > > > Formula is what I am looking for, but I am getting a syntax error
in
> > the
> > > > line
> > > > > If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
> > > > > I have copied and pasted, should I be doing something else?
Thanks
> > > >
> > > >
> > > > Yes, I am right clicking on sheet tab and clicking on View Code and
then
> > pasting in formula. Thgat line always comes up red and when I go to
enter
> > in the appropriate cells on the correct sheet I gfet the Compile Error -
> > Syntax Error. Thanks
> >
> >
> > I have tried from Private Worksheet.....
> also from On Error Go....
> and I stop after End Sub
> Thanks

[Carol49]

"Bob Phillips" wrote:

> Can you ,ail me your workbook?
>
> --
> HTH
>
> Bob Phillips
>
> "Carol49" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > "Bob Phillips" wrote:
> >
> > > I am confused, it is fine here.
> > >
> > > You say you pasted the formula. What exactly have you copied in?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Carol49" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Have you inserted it in a worksheet code module as instructed?
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "Carol49" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > >
> > > > > >
> > > > > > "Carol49" wrote:
> > > > > >
> > > > > > > Need formula that will allow a number e.g. 5 to be placed in a
> cell,
> > > > > which
> > > > > > > t\will then insert 5 rows. Thanks
> > > > > > Formula is what I am looking for, but I am getting a syntax error
> in
> > > the
> > > > > line
> > > > > > If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
> > > > > > I have copied and pasted, should I be doing something else?
> Thanks
> > > > >
> > > > >
> > > > > Yes, I am right clicking on sheet tab and clicking on View Code and
> then
> > > pasting in formula. Thgat line always comes up red and when I go to
> enter
> > > in the appropriate cells on the correct sheet I gfet the Compile Error -
> > > Syntax Error. Thanks
> > >
> > >
> > > I have tried from Private Worksheet.....
> > also from On Error Go....
> > and I stop after End Sub
> > Thanks
>
>
> Yes, I can. Where do I email it to?

[Bob Phillips]

bob dot phillips at tiscali dot co dot uk

do the obvious with it

--
HTH

Bob Phillips

"Carol49" <[email protected]> wrote in message
news:[email protected]...
>
>
> "Bob Phillips" wrote:
>
> > Can you ,ail me your workbook?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Carol49" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > I am confused, it is fine here.
> > > >
> > > > You say you pasted the formula. What exactly have you copied in?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Carol49" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Have you inserted it in a worksheet code module as instructed?
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > "Carol49" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > >
> > > > > > >
> > > > > > > "Carol49" wrote:
> > > > > > >
> > > > > > > > Need formula that will allow a number e.g. 5 to be placed in
a
> > cell,
> > > > > > which
> > > > > > > > t\will then insert 5 rows. Thanks
> > > > > > > Formula is what I am looking for, but I am getting a syntax
error
> > in
> > > > the
> > > > > > line
> > > > > > > If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
> > > > > > > I have copied and pasted, should I be doing something else?
> > Thanks
> > > > > >
> > > > > >
> > > > > > Yes, I am right clicking on sheet tab and clicking on View Code
and
> > then
> > > > pasting in formula. Thgat line always comes up red and when I go to
> > enter
> > > > in the appropriate cells on the correct sheet I gfet the Compile
Error -
> > > > Syntax Error. Thanks
> > > >
> > > >
> > > > I have tried from Private Worksheet.....
> > > also from On Error Go....
> > > and I stop after End Sub
> > > Thanks
> >
> >
> > Yes, I can. Where do I email it to?