# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Macro to insert row.

## Steve

I would like to set up a macro to insert a blank row at specific spots in a
worksheet. For example, if cell a10 contains 991CX, i would like to have a
blank row inserted directly above it. Thanks in advance for any help you are
able to offer.

Steve

----------


## Greg Wilson

Assuming the data is all in column A and starts in cell A2 and there are no
blanks in the data:

Sub InsertRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
Cells(i, 1).EntireRow.Insert
i = i + 2
Else
i = i + 1
End If
Loop
End Sub

Regards,
Greg


"Steve" wrote:

> I would like to set up a macro to insert a blank row at specific spots in a
> worksheet. For example, if cell a10 contains 991CX, i would like to have a
> blank row inserted directly above it. Thanks in advance for any help you are
> able to offer.
>
> Steve

----------


## Rowan

One way is like this:

Sub InsRow()
With Range("A10")
If .Value = "991CX" Then
.EntireRow.Insert
End If
End With
End Sub

otherwise:

Sub InsRow()
If Cells(10, 1).Value = "991CX" Then
Cells(10, 1).EntireRow.Insert
End If
End Sub

Hope this helps
Rowan


Hope this helps
Rowan

"Steve" wrote:

> I would like to set up a macro to insert a blank row at specific spots in a
> worksheet. For example, if cell a10 contains 991CX, i would like to have a
> blank row inserted directly above it. Thanks in advance for any help you are
> able to offer.
>
> Steve

----------


## Steve

The value 991CX will not always be in a10. It will always be in column A.
Just not always row 10. I am looking for something that will search col. A
for 991CX, when found, insert a blank row above it. Sorry for the confusion
guys.

Steve

"Rowan" wrote:

> One way is like this:
>
> Sub InsRow()
>     With Range("A10")
>         If .Value = "991CX" Then
>             .EntireRow.Insert
>         End If
>     End With
> End Sub
>
> otherwise:
>
> Sub InsRow()
>     If Cells(10, 1).Value = "991CX" Then
>         Cells(10, 1).EntireRow.Insert
>     End If
> End Sub
>
> Hope this helps
> Rowan
>
>
> Hope this helps
> Rowan
>
> "Steve" wrote:
>
> > I would like to set up a macro to insert a blank row at specific spots in a
> > worksheet. For example, if cell a10 contains 991CX, i would like to have a
> > blank row inserted directly above it. Thanks in advance for any help you are
> > able to offer.
> >
> > Steve

----------


## Greg Wilson

Have you tested mine? Mine does this.

Regards,
Greg

"Steve" wrote:

> The value 991CX will not always be in a10. It will always be in column A.
> Just not always row 10. I am looking for something that will search col. A
> for 991CX, when found, insert a blank row above it. Sorry for the confusion
> guys.
>
> Steve
>
> "Rowan" wrote:
>
> > One way is like this:
> >
> > Sub InsRow()
> >     With Range("A10")
> >         If .Value = "991CX" Then
> >             .EntireRow.Insert
> >         End If
> >     End With
> > End Sub
> >
> > otherwise:
> >
> > Sub InsRow()
> >     If Cells(10, 1).Value = "991CX" Then
> >         Cells(10, 1).EntireRow.Insert
> >     End If
> > End Sub
> >
> > Hope this helps
> > Rowan
> >
> >
> > Hope this helps
> > Rowan
> >
> > "Steve" wrote:
> >
> > > I would like to set up a macro to insert a blank row at specific spots in a
> > > worksheet. For example, if cell a10 contains 991CX, i would like to have a
> > > blank row inserted directly above it. Thanks in advance for any help you are
> > > able to offer.
> > >
> > > Steve

----------


## STEVE BELL

Greg,

I particularly like your code - it gets around the mess that usually hangs
up a loop when you step down the rows and do some inserting along the way.

I'm used to starting at the bottom and working up...
--
steveB

Remove "AYN" from email to respond
"Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
news:38C8810D-010D-469D-AA50-01ACE4D145CE@microsoft.com...
> Have you tested mine? Mine does this.
>
> Regards,
> Greg
>
> "Steve" wrote:
>
>> The value 991CX will not always be in a10. It will always be in column A.
>> Just not always row 10. I am looking for something that will search col.
>> A
>> for 991CX, when found, insert a blank row above it. Sorry for the
>> confusion
>> guys.
>>
>> Steve
>>
>> "Rowan" wrote:
>>
>> > One way is like this:
>> >
>> > Sub InsRow()
>> >     With Range("A10")
>> >         If .Value = "991CX" Then
>> >             .EntireRow.Insert
>> >         End If
>> >     End With
>> > End Sub
>> >
>> > otherwise:
>> >
>> > Sub InsRow()
>> >     If Cells(10, 1).Value = "991CX" Then
>> >         Cells(10, 1).EntireRow.Insert
>> >     End If
>> > End Sub
>> >
>> > Hope this helps
>> > Rowan
>> >
>> >
>> > Hope this helps
>> > Rowan
>> >
>> > "Steve" wrote:
>> >
>> > > I would like to set up a macro to insert a blank row at specific
>> > > spots in a
>> > > worksheet. For example, if cell a10 contains 991CX, i would like to
>> > > have a
>> > > blank row inserted directly above it. Thanks in advance for any help
>> > > you are
>> > > able to offer.
>> > >
>> > > Steve

----------


## Steve

When I ran it it nothing happened.

"Greg Wilson" wrote:

> Have you tested mine? Mine does this.
>
> Regards,
> Greg
>
> "Steve" wrote:
>
> > The value 991CX will not always be in a10. It will always be in column A.
> > Just not always row 10. I am looking for something that will search col. A
> > for 991CX, when found, insert a blank row above it. Sorry for the confusion
> > guys.
> >
> > Steve
> >
> > "Rowan" wrote:
> >
> > > One way is like this:
> > >
> > > Sub InsRow()
> > >     With Range("A10")
> > >         If .Value = "991CX" Then
> > >             .EntireRow.Insert
> > >         End If
> > >     End With
> > > End Sub
> > >
> > > otherwise:
> > >
> > > Sub InsRow()
> > >     If Cells(10, 1).Value = "991CX" Then
> > >         Cells(10, 1).EntireRow.Insert
> > >     End If
> > > End Sub
> > >
> > > Hope this helps
> > > Rowan
> > >
> > >
> > > Hope this helps
> > > Rowan
> > >
> > > "Steve" wrote:
> > >
> > > > I would like to set up a macro to insert a blank row at specific spots in a
> > > > worksheet. For example, if cell a10 contains 991CX, i would like to have a
> > > > blank row inserted directly above it. Thanks in advance for any help you are
> > > > able to offer.
> > > >
> > > > Steve

----------


## STEVE BELL

Steve,

Greg's code should work if any cell in column A contains (only) 991CX
If there is more in the cell, than it won't work.

Have you tried testing the code using the F8 key and stepping through it.
Set up a dummy sheet with some cells in column A = 991CX and see what
happens.
(you only need a few rows).

See if you can locate an error or something and get back to us.

Sub InsertRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
Cells(i, 1).EntireRow.Insert
i = i + 2
Else
i = i + 1
End If
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
news:8ACF1BBA-4B3B-4B72-9C45-A5E5AA352AC9@microsoft.com...
> When I ran it it nothing happened.
>
> "Greg Wilson" wrote:
>
>> Have you tested mine? Mine does this.
>>
>> Regards,
>> Greg
>>
>> "Steve" wrote:
>>
>> > The value 991CX will not always be in a10. It will always be in column
>> > A.
>> > Just not always row 10. I am looking for something that will search
>> > col. A
>> > for 991CX, when found, insert a blank row above it. Sorry for the
>> > confusion
>> > guys.
>> >
>> > Steve
>> >
>> > "Rowan" wrote:
>> >
>> > > One way is like this:
>> > >
>> > > Sub InsRow()
>> > >     With Range("A10")
>> > >         If .Value = "991CX" Then
>> > >             .EntireRow.Insert
>> > >         End If
>> > >     End With
>> > > End Sub
>> > >
>> > > otherwise:
>> > >
>> > > Sub InsRow()
>> > >     If Cells(10, 1).Value = "991CX" Then
>> > >         Cells(10, 1).EntireRow.Insert
>> > >     End If
>> > > End Sub
>> > >
>> > > Hope this helps
>> > > Rowan
>> > >
>> > >
>> > > Hope this helps
>> > > Rowan
>> > >
>> > > "Steve" wrote:
>> > >
>> > > > I would like to set up a macro to insert a blank row at specific
>> > > > spots in a
>> > > > worksheet. For example, if cell a10 contains 991CX, i would like to
>> > > > have a
>> > > > blank row inserted directly above it. Thanks in advance for any
>> > > > help you are
>> > > > able to offer.
>> > > >
>> > > > Steve

----------


## Steve

I apologize guys. I am not trying to be a pain. Column A only has Certian
Aircraft tail numbers in it. Column B:G is set up with different sets of
information. I am not sure why. But i can't get this to run.

"STEVE BELL" wrote:

> Steve,
>
> Greg's code should work if any cell in column A contains (only) 991CX
> If there is more in the cell, than it won't work.
>
> Have you tried testing the code using the F8 key and stepping through it.
> Set up a dummy sheet with some cells in column A = 991CX and see what
> happens.
> (you only need a few rows).
>
> See if you can locate an error or something and get back to us.
>
> Sub InsertRows()
> Dim i As Long
> i = 2
> Do Until Trim(Cells(i, 1)) = ""
>     If Cells(i, 1) = "991CX" Then
>         Cells(i, 1).EntireRow.Insert
>         i = i + 2
>     Else
>         i = i + 1
>     End If
> Loop
> End Sub
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
> news:8ACF1BBA-4B3B-4B72-9C45-A5E5AA352AC9@microsoft.com...
> > When I ran it it nothing happened.
> >
> > "Greg Wilson" wrote:
> >
> >> Have you tested mine? Mine does this.
> >>
> >> Regards,
> >> Greg
> >>
> >> "Steve" wrote:
> >>
> >> > The value 991CX will not always be in a10. It will always be in column
> >> > A.
> >> > Just not always row 10. I am looking for something that will search
> >> > col. A
> >> > for 991CX, when found, insert a blank row above it. Sorry for the
> >> > confusion
> >> > guys.
> >> >
> >> > Steve
> >> >
> >> > "Rowan" wrote:
> >> >
> >> > > One way is like this:
> >> > >
> >> > > Sub InsRow()
> >> > >     With Range("A10")
> >> > >         If .Value = "991CX" Then
> >> > >             .EntireRow.Insert
> >> > >         End If
> >> > >     End With
> >> > > End Sub
> >> > >
> >> > > otherwise:
> >> > >
> >> > > Sub InsRow()
> >> > >     If Cells(10, 1).Value = "991CX" Then
> >> > >         Cells(10, 1).EntireRow.Insert
> >> > >     End If
> >> > > End Sub
> >> > >
> >> > > Hope this helps
> >> > > Rowan
> >> > >
> >> > >
> >> > > Hope this helps
> >> > > Rowan
> >> > >
> >> > > "Steve" wrote:
> >> > >
> >> > > > I would like to set up a macro to insert a blank row at specific
> >> > > > spots in a
> >> > > > worksheet. For example, if cell a10 contains 991CX, i would like to
> >> > > > have a
> >> > > > blank row inserted directly above it. Thanks in advance for any
> >> > > > help you are
> >> > > > able to offer.
> >> > > >
> >> > > > Steve
>
>
>

----------


## STEVE BELL

Steve,

No need to apologize!  We just aren't sure of what is happening.

Open a new work book

Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the new
workbook.
Make sure at least one row in column A has 991CX
Put this code in a module and run it - you should get a message
for each row.

Tell us what happens...

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
Else
Msgbox Cells(i,1).address & " contains " & cells(i,1).Text & ".end
of text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
news:0B93D775-4621-4CB8-B8DF-ABF703F14DFC@microsoft.com...
>I apologize guys. I am not trying to be a pain. Column A only has Certian
> Aircraft tail numbers in it. Column B:G is set up with different sets of
> information. I am not sure why. But i can't get this to run.
>
> "STEVE BELL" wrote:
>
>> Steve,
>>
>> Greg's code should work if any cell in column A contains (only) 991CX
>> If there is more in the cell, than it won't work.
>>
>> Have you tried testing the code using the F8 key and stepping through it.
>> Set up a dummy sheet with some cells in column A = 991CX and see what
>> happens.
>> (you only need a few rows).
>>
>> See if you can locate an error or something and get back to us.
>>
>> Sub InsertRows()
>> Dim i As Long
>> i = 2
>> Do Until Trim(Cells(i, 1)) = ""
>>     If Cells(i, 1) = "991CX" Then
>>         Cells(i, 1).EntireRow.Insert
>>         i = i + 2
>>     Else
>>         i = i + 1
>>     End If
>> Loop
>> End Sub
>>
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
>> news:8ACF1BBA-4B3B-4B72-9C45-A5E5AA352AC9@microsoft.com...
>> > When I ran it it nothing happened.
>> >
>> > "Greg Wilson" wrote:
>> >
>> >> Have you tested mine? Mine does this.
>> >>
>> >> Regards,
>> >> Greg
>> >>
>> >> "Steve" wrote:
>> >>
>> >> > The value 991CX will not always be in a10. It will always be in
>> >> > column
>> >> > A.
>> >> > Just not always row 10. I am looking for something that will search
>> >> > col. A
>> >> > for 991CX, when found, insert a blank row above it. Sorry for the
>> >> > confusion
>> >> > guys.
>> >> >
>> >> > Steve
>> >> >
>> >> > "Rowan" wrote:
>> >> >
>> >> > > One way is like this:
>> >> > >
>> >> > > Sub InsRow()
>> >> > >     With Range("A10")
>> >> > >         If .Value = "991CX" Then
>> >> > >             .EntireRow.Insert
>> >> > >         End If
>> >> > >     End With
>> >> > > End Sub
>> >> > >
>> >> > > otherwise:
>> >> > >
>> >> > > Sub InsRow()
>> >> > >     If Cells(10, 1).Value = "991CX" Then
>> >> > >         Cells(10, 1).EntireRow.Insert
>> >> > >     End If
>> >> > > End Sub
>> >> > >
>> >> > > Hope this helps
>> >> > > Rowan
>> >> > >
>> >> > >
>> >> > > Hope this helps
>> >> > > Rowan
>> >> > >
>> >> > > "Steve" wrote:
>> >> > >
>> >> > > > I would like to set up a macro to insert a blank row at specific
>> >> > > > spots in a
>> >> > > > worksheet. For example, if cell a10 contains 991CX, i would like
>> >> > > > to
>> >> > > > have a
>> >> > > > blank row inserted directly above it. Thanks in advance for any
>> >> > > > help you are
>> >> > > > able to offer.
>> >> > > >
>> >> > > > Steve
>>
>>
>>

----------


## Steve

Msgbox Cells(i, 1).Text & " " Cells(i,1).address
This time i received a syntax error at the following line:


"STEVE BELL" wrote:

> Steve,
>
> No need to apologize!  We just aren't sure of what is happening.
>
> Open a new work book
>
> Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the new
> workbook.
> Make sure at least one row in column A has 991CX
> Put this code in a module and run it - you should get a message
> for each row.
>
> Tell us what happens...
>
> Sub CheckRows()
> Dim i As Long
> i = 2
> Do Until Trim(Cells(i, 1)) = ""
>     If Cells(i, 1) = "991CX" Then
>         Msgbox Cells(i, 1).Text & " " Cells(i,1).address
>     Else
>         Msgbox Cells(i,1).address & " contains " & cells(i,1).Text & ".end
> of text"
>     End If
>         i = i + 1
> Loop
> End Sub
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
> news:0B93D775-4621-4CB8-B8DF-ABF703F14DFC@microsoft.com...
> >I apologize guys. I am not trying to be a pain. Column A only has Certian
> > Aircraft tail numbers in it. Column B:G is set up with different sets of
> > information. I am not sure why. But i can't get this to run.
> >
> > "STEVE BELL" wrote:
> >
> >> Steve,
> >>
> >> Greg's code should work if any cell in column A contains (only) 991CX
> >> If there is more in the cell, than it won't work.
> >>
> >> Have you tried testing the code using the F8 key and stepping through it.
> >> Set up a dummy sheet with some cells in column A = 991CX and see what
> >> happens.
> >> (you only need a few rows).
> >>
> >> See if you can locate an error or something and get back to us.
> >>
> >> Sub InsertRows()
> >> Dim i As Long
> >> i = 2
> >> Do Until Trim(Cells(i, 1)) = ""
> >>     If Cells(i, 1) = "991CX" Then
> >>         Cells(i, 1).EntireRow.Insert
> >>         i = i + 2
> >>     Else
> >>         i = i + 1
> >>     End If
> >> Loop
> >> End Sub
> >>
> >> --
> >> steveB
> >>
> >> Remove "AYN" from email to respond
> >> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
> >> news:8ACF1BBA-4B3B-4B72-9C45-A5E5AA352AC9@microsoft.com...
> >> > When I ran it it nothing happened.
> >> >
> >> > "Greg Wilson" wrote:
> >> >
> >> >> Have you tested mine? Mine does this.
> >> >>
> >> >> Regards,
> >> >> Greg
> >> >>
> >> >> "Steve" wrote:
> >> >>
> >> >> > The value 991CX will not always be in a10. It will always be in
> >> >> > column
> >> >> > A.
> >> >> > Just not always row 10. I am looking for something that will search
> >> >> > col. A
> >> >> > for 991CX, when found, insert a blank row above it. Sorry for the
> >> >> > confusion
> >> >> > guys.
> >> >> >
> >> >> > Steve
> >> >> >
> >> >> > "Rowan" wrote:
> >> >> >
> >> >> > > One way is like this:
> >> >> > >
> >> >> > > Sub InsRow()
> >> >> > >     With Range("A10")
> >> >> > >         If .Value = "991CX" Then
> >> >> > >             .EntireRow.Insert
> >> >> > >         End If
> >> >> > >     End With
> >> >> > > End Sub
> >> >> > >
> >> >> > > otherwise:
> >> >> > >
> >> >> > > Sub InsRow()
> >> >> > >     If Cells(10, 1).Value = "991CX" Then
> >> >> > >         Cells(10, 1).EntireRow.Insert
> >> >> > >     End If
> >> >> > > End Sub
> >> >> > >
> >> >> > > Hope this helps
> >> >> > > Rowan
> >> >> > >
> >> >> > >
> >> >> > > Hope this helps
> >> >> > > Rowan
> >> >> > >
> >> >> > > "Steve" wrote:
> >> >> > >
> >> >> > > > I would like to set up a macro to insert a blank row at specific
> >> >> > > > spots in a
> >> >> > > > worksheet. For example, if cell a10 contains 991CX, i would like
> >> >> > > > to
> >> >> > > > have a
> >> >> > > > blank row inserted directly above it. Thanks in advance for any
> >> >> > > > help you are
> >> >> > > > able to offer.
> >> >> > > >
> >> >> > > > Steve
> >>
> >>
> >>
>
>
>

----------


## STEVE BELL

Steve,

My bad - left out an "&"
Also checked it out to see if it would run - and it did.
Also removed all tabs and excessive spaces to make copy & paste better....

Now try it out... And get back to us....

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
MsgBox Cells(i, 1).Text & " " & Cells(i, 1).Address
Else
MsgBox Cells(i, 1).Address & " contains " & Cells(i, 1).Text & ".end of
text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
news:35DA690F-370A-4A2A-AB0D-2D91CF438BB4@microsoft.com...
> Msgbox Cells(i, 1).Text & " " Cells(i,1).address
> This time i received a syntax error at the following line:
>
>
> "STEVE BELL" wrote:
>
>> Steve,
>>
>> No need to apologize!  We just aren't sure of what is happening.
>>
>> Open a new work book
>>
>> Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the new
>> workbook.
>> Make sure at least one row in column A has 991CX
>> Put this code in a module and run it - you should get a message
>> for each row.
>>
>> Tell us what happens...
>>
>> Sub CheckRows()
>> Dim i As Long
>> i = 2
>> Do Until Trim(Cells(i, 1)) = ""
>>     If Cells(i, 1) = "991CX" Then
>>         Msgbox Cells(i, 1).Text & " " Cells(i,1).address
>>     Else
>>         Msgbox Cells(i,1).address & " contains " & cells(i,1).Text &
>> ".end
>> of text"
>>     End If
>>         i = i + 1
>> Loop
>> End Sub
>>
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
>> news:0B93D775-4621-4CB8-B8DF-ABF703F14DFC@microsoft.com...
>> >I apologize guys. I am not trying to be a pain. Column A only has
>> >Certian
>> > Aircraft tail numbers in it. Column B:G is set up with different sets
>> > of
>> > information. I am not sure why. But i can't get this to run.
>> >
>> > "STEVE BELL" wrote:
>> >
>> >> Steve,
>> >>
>> >> Greg's code should work if any cell in column A contains (only) 991CX
>> >> If there is more in the cell, than it won't work.
>> >>
>> >> Have you tried testing the code using the F8 key and stepping through
>> >> it.
>> >> Set up a dummy sheet with some cells in column A = 991CX and see what
>> >> happens.
>> >> (you only need a few rows).
>> >>
>> >> See if you can locate an error or something and get back to us.
>> >>
>> >> Sub InsertRows()
>> >> Dim i As Long
>> >> i = 2
>> >> Do Until Trim(Cells(i, 1)) = ""
>> >>     If Cells(i, 1) = "991CX" Then
>> >>         Cells(i, 1).EntireRow.Insert
>> >>         i = i + 2
>> >>     Else
>> >>         i = i + 1
>> >>     End If
>> >> Loop
>> >> End Sub
>> >>
>> >> --
>> >> steveB
>> >>
>> >> Remove "AYN" from email to respond
>> >> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
>> >> news:8ACF1BBA-4B3B-4B72-9C45-A5E5AA352AC9@microsoft.com...
>> >> > When I ran it it nothing happened.
>> >> >
>> >> > "Greg Wilson" wrote:
>> >> >
>> >> >> Have you tested mine? Mine does this.
>> >> >>
>> >> >> Regards,
>> >> >> Greg
>> >> >>
>> >> >> "Steve" wrote:
>> >> >>
>> >> >> > The value 991CX will not always be in a10. It will always be in
>> >> >> > column
>> >> >> > A.
>> >> >> > Just not always row 10. I am looking for something that will
>> >> >> > search
>> >> >> > col. A
>> >> >> > for 991CX, when found, insert a blank row above it. Sorry for the
>> >> >> > confusion
>> >> >> > guys.
>> >> >> >
>> >> >> > Steve
>> >> >> >
>> >> >> > "Rowan" wrote:
>> >> >> >
>> >> >> > > One way is like this:
>> >> >> > >
>> >> >> > > Sub InsRow()
>> >> >> > >     With Range("A10")
>> >> >> > >         If .Value = "991CX" Then
>> >> >> > >             .EntireRow.Insert
>> >> >> > >         End If
>> >> >> > >     End With
>> >> >> > > End Sub
>> >> >> > >
>> >> >> > > otherwise:
>> >> >> > >
>> >> >> > > Sub InsRow()
>> >> >> > >     If Cells(10, 1).Value = "991CX" Then
>> >> >> > >         Cells(10, 1).EntireRow.Insert
>> >> >> > >     End If
>> >> >> > > End Sub
>> >> >> > >
>> >> >> > > Hope this helps
>> >> >> > > Rowan
>> >> >> > >
>> >> >> > >
>> >> >> > > Hope this helps
>> >> >> > > Rowan
>> >> >> > >
>> >> >> > > "Steve" wrote:
>> >> >> > >
>> >> >> > > > I would like to set up a macro to insert a blank row at
>> >> >> > > > specific
>> >> >> > > > spots in a
>> >> >> > > > worksheet. For example, if cell a10 contains 991CX, i would
>> >> >> > > > like
>> >> >> > > > to
>> >> >> > > > have a
>> >> >> > > > blank row inserted directly above it. Thanks in advance for
>> >> >> > > > any
>> >> >> > > > help you are
>> >> >> > > > able to offer.
>> >> >> > > >
>> >> >> > > > Steve
>> >>
>> >>
>> >>
>>
>>
>>

----------


## Steve

I'm baffled. Nothing is happening. I am copying the info. Pasting it into a
module. I then go back to the wksht and run the macro using the tools menu.

"STEVE BELL" wrote:

> Steve,
>
> My bad - left out an "&"
> Also checked it out to see if it would run - and it did.
> Also removed all tabs and excessive spaces to make copy & paste better....
>
> Now try it out... And get back to us....
>
> Sub CheckRows()
> Dim i As Long
> i = 2
> Do Until Trim(Cells(i, 1)) = ""
> If Cells(i, 1) = "991CX" Then
> MsgBox Cells(i, 1).Text & " " & Cells(i, 1).Address
> Else
> MsgBox Cells(i, 1).Address & " contains " & Cells(i, 1).Text & ".end of
> text"
> End If
> i = i + 1
> Loop
> End Sub
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
> news:35DA690F-370A-4A2A-AB0D-2D91CF438BB4@microsoft.com...
> > Msgbox Cells(i, 1).Text & " " Cells(i,1).address
> > This time i received a syntax error at the following line:
> >
> >
> > "STEVE BELL" wrote:
> >
> >> Steve,
> >>
> >> No need to apologize!  We just aren't sure of what is happening.
> >>
> >> Open a new work book
> >>
> >> Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the new
> >> workbook.
> >> Make sure at least one row in column A has 991CX
> >> Put this code in a module and run it - you should get a message
> >> for each row.
> >>
> >> Tell us what happens...
> >>
> >> Sub CheckRows()
> >> Dim i As Long
> >> i = 2
> >> Do Until Trim(Cells(i, 1)) = ""
> >>     If Cells(i, 1) = "991CX" Then
> >>         Msgbox Cells(i, 1).Text & " " Cells(i,1).address
> >>     Else
> >>         Msgbox Cells(i,1).address & " contains " & cells(i,1).Text &
> >> ".end
> >> of text"
> >>     End If
> >>         i = i + 1
> >> Loop
> >> End Sub
> >>
> >> --
> >> steveB
> >>
> >> Remove "AYN" from email to respond
> >> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
> >> news:0B93D775-4621-4CB8-B8DF-ABF703F14DFC@microsoft.com...
> >> >I apologize guys. I am not trying to be a pain. Column A only has
> >> >Certian
> >> > Aircraft tail numbers in it. Column B:G is set up with different sets
> >> > of
> >> > information. I am not sure why. But i can't get this to run.
> >> >
> >> > "STEVE BELL" wrote:
> >> >
> >> >> Steve,
> >> >>
> >> >> Greg's code should work if any cell in column A contains (only) 991CX
> >> >> If there is more in the cell, than it won't work.
> >> >>
> >> >> Have you tried testing the code using the F8 key and stepping through
> >> >> it.
> >> >> Set up a dummy sheet with some cells in column A = 991CX and see what
> >> >> happens.
> >> >> (you only need a few rows).
> >> >>
> >> >> See if you can locate an error or something and get back to us.
> >> >>
> >> >> Sub InsertRows()
> >> >> Dim i As Long
> >> >> i = 2
> >> >> Do Until Trim(Cells(i, 1)) = ""
> >> >>     If Cells(i, 1) = "991CX" Then
> >> >>         Cells(i, 1).EntireRow.Insert
> >> >>         i = i + 2
> >> >>     Else
> >> >>         i = i + 1
> >> >>     End If
> >> >> Loop
> >> >> End Sub
> >> >>
> >> >> --
> >> >> steveB
> >> >>
> >> >> Remove "AYN" from email to respond
> >> >> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
> >> >> news:8ACF1BBA-4B3B-4B72-9C45-A5E5AA352AC9@microsoft.com...
> >> >> > When I ran it it nothing happened.
> >> >> >
> >> >> > "Greg Wilson" wrote:
> >> >> >
> >> >> >> Have you tested mine? Mine does this.
> >> >> >>
> >> >> >> Regards,
> >> >> >> Greg
> >> >> >>
> >> >> >> "Steve" wrote:
> >> >> >>
> >> >> >> > The value 991CX will not always be in a10. It will always be in
> >> >> >> > column
> >> >> >> > A.
> >> >> >> > Just not always row 10. I am looking for something that will
> >> >> >> > search
> >> >> >> > col. A
> >> >> >> > for 991CX, when found, insert a blank row above it. Sorry for the
> >> >> >> > confusion
> >> >> >> > guys.
> >> >> >> >
> >> >> >> > Steve
> >> >> >> >
> >> >> >> > "Rowan" wrote:
> >> >> >> >
> >> >> >> > > One way is like this:
> >> >> >> > >
> >> >> >> > > Sub InsRow()
> >> >> >> > >     With Range("A10")
> >> >> >> > >         If .Value = "991CX" Then
> >> >> >> > >             .EntireRow.Insert
> >> >> >> > >         End If
> >> >> >> > >     End With
> >> >> >> > > End Sub
> >> >> >> > >
> >> >> >> > > otherwise:
> >> >> >> > >
> >> >> >> > > Sub InsRow()
> >> >> >> > >     If Cells(10, 1).Value = "991CX" Then
> >> >> >> > >         Cells(10, 1).EntireRow.Insert
> >> >> >> > >     End If
> >> >> >> > > End Sub
> >> >> >> > >
> >> >> >> > > Hope this helps
> >> >> >> > > Rowan
> >> >> >> > >
> >> >> >> > >
> >> >> >> > > Hope this helps
> >> >> >> > > Rowan
> >> >> >> > >
> >> >> >> > > "Steve" wrote:
> >> >> >> > >
> >> >> >> > > > I would like to set up a macro to insert a blank row at
> >> >> >> > > > specific
> >> >> >> > > > spots in a
> >> >> >> > > > worksheet. For example, if cell a10 contains 991CX, i would
> >> >> >> > > > like
> >> >> >> > > > to
> >> >> >> > > > have a
> >> >> >> > > > blank row inserted directly above it. Thanks in advance for
> >> >> >> > > > any
> >> >> >> > > > help you are
> >> >> >> > > > able to offer.
> >> >> >> > > >
> >> >> >> > > > Steve
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

----------


## STEVE BELL

Steve,

Let's do this the hard way -
send a copy of your workbook or worksheet to my email
and I'll work with it until it works....

rand451@verizon.net

--
steveB

Remove "AYN" from email to respond
"Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
news:C6D54C1B-7AAA-4CC5-9B63-E26CB1A3083F@microsoft.com...
> I'm baffled. Nothing is happening. I am copying the info. Pasting it into
> a
> module. I then go back to the wksht and run the macro using the tools
> menu.
>
> "STEVE BELL" wrote:
>
>> Steve,
>>
>> My bad - left out an "&"
>> Also checked it out to see if it would run - and it did.
>> Also removed all tabs and excessive spaces to make copy & paste
>> better....
>>
>> Now try it out... And get back to us....
>>
>> Sub CheckRows()
>> Dim i As Long
>> i = 2
>> Do Until Trim(Cells(i, 1)) = ""
>> If Cells(i, 1) = "991CX" Then
>> MsgBox Cells(i, 1).Text & " " & Cells(i, 1).Address
>> Else
>> MsgBox Cells(i, 1).Address & " contains " & Cells(i, 1).Text & ".end of
>> text"
>> End If
>> i = i + 1
>> Loop
>> End Sub
>>
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
>> news:35DA690F-370A-4A2A-AB0D-2D91CF438BB4@microsoft.com...
>> > Msgbox Cells(i, 1).Text & " " Cells(i,1).address
>> > This time i received a syntax error at the following line:
>> >
>> >
>> > "STEVE BELL" wrote:
>> >
>> >> Steve,
>> >>
>> >> No need to apologize!  We just aren't sure of what is happening.
>> >>
>> >> Open a new work book
>> >>
>> >> Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the
>> >> new
>> >> workbook.
>> >> Make sure at least one row in column A has 991CX
>> >> Put this code in a module and run it - you should get a message
>> >> for each row.
>> >>
>> >> Tell us what happens...
>> >>
>> >> Sub CheckRows()
>> >> Dim i As Long
>> >> i = 2
>> >> Do Until Trim(Cells(i, 1)) = ""
>> >>     If Cells(i, 1) = "991CX" Then
>> >>         Msgbox Cells(i, 1).Text & " " Cells(i,1).address
>> >>     Else
>> >>         Msgbox Cells(i,1).address & " contains " & cells(i,1).Text &
>> >> ".end
>> >> of text"
>> >>     End If
>> >>         i = i + 1
>> >> Loop
>> >> End Sub
>> >>
>> >> --
>> >> steveB
>> >>
>> >> Remove "AYN" from email to respond
>> >> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
>> >> news:0B93D775-4621-4CB8-B8DF-ABF703F14DFC@microsoft.com...
>> >> >I apologize guys. I am not trying to be a pain. Column A only has
>> >> >Certian
>> >> > Aircraft tail numbers in it. Column B:G is set up with different
>> >> > sets
>> >> > of
>> >> > information. I am not sure why. But i can't get this to run.
>> >> >
>> >> > "STEVE BELL" wrote:
>> >> >
>> >> >> Steve,
>> >> >>
>> >> >> Greg's code should work if any cell in column A contains (only)
>> >> >> 991CX
>> >> >> If there is more in the cell, than it won't work.
>> >> >>
>> >> >> Have you tried testing the code using the F8 key and stepping
>> >> >> through
>> >> >> it.
>> >> >> Set up a dummy sheet with some cells in column A = 991CX and see
>> >> >> what
>> >> >> happens.
>> >> >> (you only need a few rows).
>> >> >>
>> >> >> See if you can locate an error or something and get back to us.
>> >> >>
>> >> >> Sub InsertRows()
>> >> >> Dim i As Long
>> >> >> i = 2
>> >> >> Do Until Trim(Cells(i, 1)) = ""
>> >> >>     If Cells(i, 1) = "991CX" Then
>> >> >>         Cells(i, 1).EntireRow.Insert
>> >> >>         i = i + 2
>> >> >>     Else
>> >> >>         i = i + 1
>> >> >>     End If
>> >> >> Loop
>> >> >> End Sub
>> >> >>
>> >> >> --
>> >> >> steveB
>> >> >>
>> >> >> Remove "AYN" from email to respond
>> >> >> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
>> >> >> news:8ACF1BBA-4B3B-4B72-9C45-A5E5AA352AC9@microsoft.com...
>> >> >> > When I ran it it nothing happened.
>> >> >> >
>> >> >> > "Greg Wilson" wrote:
>> >> >> >
>> >> >> >> Have you tested mine? Mine does this.
>> >> >> >>
>> >> >> >> Regards,
>> >> >> >> Greg
>> >> >> >>
>> >> >> >> "Steve" wrote:
>> >> >> >>
>> >> >> >> > The value 991CX will not always be in a10. It will always be
>> >> >> >> > in
>> >> >> >> > column
>> >> >> >> > A.
>> >> >> >> > Just not always row 10. I am looking for something that will
>> >> >> >> > search
>> >> >> >> > col. A
>> >> >> >> > for 991CX, when found, insert a blank row above it. Sorry for
>> >> >> >> > the
>> >> >> >> > confusion
>> >> >> >> > guys.
>> >> >> >> >
>> >> >> >> > Steve
>> >> >> >> >
>> >> >> >> > "Rowan" wrote:
>> >> >> >> >
>> >> >> >> > > One way is like this:
>> >> >> >> > >
>> >> >> >> > > Sub InsRow()
>> >> >> >> > >     With Range("A10")
>> >> >> >> > >         If .Value = "991CX" Then
>> >> >> >> > >             .EntireRow.Insert
>> >> >> >> > >         End If
>> >> >> >> > >     End With
>> >> >> >> > > End Sub
>> >> >> >> > >
>> >> >> >> > > otherwise:
>> >> >> >> > >
>> >> >> >> > > Sub InsRow()
>> >> >> >> > >     If Cells(10, 1).Value = "991CX" Then
>> >> >> >> > >         Cells(10, 1).EntireRow.Insert
>> >> >> >> > >     End If
>> >> >> >> > > End Sub
>> >> >> >> > >
>> >> >> >> > > Hope this helps
>> >> >> >> > > Rowan
>> >> >> >> > >
>> >> >> >> > >
>> >> >> >> > > Hope this helps
>> >> >> >> > > Rowan
>> >> >> >> > >
>> >> >> >> > > "Steve" wrote:
>> >> >> >> > >
>> >> >> >> > > > I would like to set up a macro to insert a blank row at
>> >> >> >> > > > specific
>> >> >> >> > > > spots in a
>> >> >> >> > > > worksheet. For example, if cell a10 contains 991CX, i
>> >> >> >> > > > would
>> >> >> >> > > > like
>> >> >> >> > > > to
>> >> >> >> > > > have a
>> >> >> >> > > > blank row inserted directly above it. Thanks in advance
>> >> >> >> > > > for
>> >> >> >> > > > any
>> >> >> >> > > > help you are
>> >> >> >> > > > able to offer.
>> >> >> >> > > >
>> >> >> >> > > > Steve
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

----------


## STEVE BELL

Steve,

I already sent you an email.

But for anyone following this thread - the problem was empty cells in Column
A.
The previous code had a loop that was designed to end when it reached a
blank cell, so... it hit
a blank cell on the 3rd row and stopped.

I changed the code to find the last used row in column A and used a For ....
Next loop
starting at the last row and using a step or -1.

Now it works....

--
steveB

Remove "AYN" from email to respond
"STEVE BELL" <AYNrand451@verizon.net> wrote in message
news:y3THe.6882$4e6.3409@trnddc04...
> Steve,
>
> Let's do this the hard way -
> send a copy of your workbook or worksheet to my email
> and I'll work with it until it works....
>
> rand451@verizon.net
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
> news:C6D54C1B-7AAA-4CC5-9B63-E26CB1A3083F@microsoft.com...
>> I'm baffled. Nothing is happening. I am copying the info. Pasting it into
>> a
>> module. I then go back to the wksht and run the macro using the tools
>> menu.
>>
>> "STEVE BELL" wrote:
>>
>>> Steve,
>>>
>>> My bad - left out an "&"
>>> Also checked it out to see if it would run - and it did.
>>> Also removed all tabs and excessive spaces to make copy & paste
>>> better....
>>>
>>> Now try it out... And get back to us....
>>>
>>> Sub CheckRows()
>>> Dim i As Long
>>> i = 2
>>> Do Until Trim(Cells(i, 1)) = ""
>>> If Cells(i, 1) = "991CX" Then
>>> MsgBox Cells(i, 1).Text & " " & Cells(i, 1).Address
>>> Else
>>> MsgBox Cells(i, 1).Address & " contains " & Cells(i, 1).Text & ".end of
>>> text"
>>> End If
>>> i = i + 1
>>> Loop
>>> End Sub
>>>
>>> --
>>> steveB
>>>
>>> Remove "AYN" from email to respond
>>> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
>>> news:35DA690F-370A-4A2A-AB0D-2D91CF438BB4@microsoft.com...
>>> > Msgbox Cells(i, 1).Text & " " Cells(i,1).address
>>> > This time i received a syntax error at the following line:
>>> >
>>> >
>>> > "STEVE BELL" wrote:
>>> >
>>> >> Steve,
>>> >>
>>> >> No need to apologize!  We just aren't sure of what is happening.
>>> >>
>>> >> Open a new work book
>>> >>
>>> >> Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the
>>> >> new
>>> >> workbook.
>>> >> Make sure at least one row in column A has 991CX
>>> >> Put this code in a module and run it - you should get a message
>>> >> for each row.
>>> >>
>>> >> Tell us what happens...
>>> >>
>>> >> Sub CheckRows()
>>> >> Dim i As Long
>>> >> i = 2
>>> >> Do Until Trim(Cells(i, 1)) = ""
>>> >>     If Cells(i, 1) = "991CX" Then
>>> >>         Msgbox Cells(i, 1).Text & " " Cells(i,1).address
>>> >>     Else
>>> >>         Msgbox Cells(i,1).address & " contains " & cells(i,1).Text &
>>> >> ".end
>>> >> of text"
>>> >>     End If
>>> >>         i = i + 1
>>> >> Loop
>>> >> End Sub
>>> >>
>>> >> --
>>> >> steveB
>>> >>
>>> >> Remove "AYN" from email to respond
>>> >> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
>>> >> news:0B93D775-4621-4CB8-B8DF-ABF703F14DFC@microsoft.com...
>>> >> >I apologize guys. I am not trying to be a pain. Column A only has
>>> >> >Certian
>>> >> > Aircraft tail numbers in it. Column B:G is set up with different
>>> >> > sets
>>> >> > of
>>> >> > information. I am not sure why. But i can't get this to run.
>>> >> >
>>> >> > "STEVE BELL" wrote:
>>> >> >
>>> >> >> Steve,
>>> >> >>
>>> >> >> Greg's code should work if any cell in column A contains (only)
>>> >> >> 991CX
>>> >> >> If there is more in the cell, than it won't work.
>>> >> >>
>>> >> >> Have you tried testing the code using the F8 key and stepping
>>> >> >> through
>>> >> >> it.
>>> >> >> Set up a dummy sheet with some cells in column A = 991CX and see
>>> >> >> what
>>> >> >> happens.
>>> >> >> (you only need a few rows).
>>> >> >>
>>> >> >> See if you can locate an error or something and get back to us.
>>> >> >>
>>> >> >> Sub InsertRows()
>>> >> >> Dim i As Long
>>> >> >> i = 2
>>> >> >> Do Until Trim(Cells(i, 1)) = ""
>>> >> >>     If Cells(i, 1) = "991CX" Then
>>> >> >>         Cells(i, 1).EntireRow.Insert
>>> >> >>         i = i + 2
>>> >> >>     Else
>>> >> >>         i = i + 1
>>> >> >>     End If
>>> >> >> Loop
>>> >> >> End Sub
>>> >> >>
>>> >> >> --
>>> >> >> steveB
>>> >> >>
>>> >> >> Remove "AYN" from email to respond
>>> >> >> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
>>> >> >> news:8ACF1BBA-4B3B-4B72-9C45-A5E5AA352AC9@microsoft.com...
>>> >> >> > When I ran it it nothing happened.
>>> >> >> >
>>> >> >> > "Greg Wilson" wrote:
>>> >> >> >
>>> >> >> >> Have you tested mine? Mine does this.
>>> >> >> >>
>>> >> >> >> Regards,
>>> >> >> >> Greg
>>> >> >> >>
>>> >> >> >> "Steve" wrote:
>>> >> >> >>
>>> >> >> >> > The value 991CX will not always be in a10. It will always be
>>> >> >> >> > in
>>> >> >> >> > column
>>> >> >> >> > A.
>>> >> >> >> > Just not always row 10. I am looking for something that will
>>> >> >> >> > search
>>> >> >> >> > col. A
>>> >> >> >> > for 991CX, when found, insert a blank row above it. Sorry for
>>> >> >> >> > the
>>> >> >> >> > confusion
>>> >> >> >> > guys.
>>> >> >> >> >
>>> >> >> >> > Steve
>>> >> >> >> >
>>> >> >> >> > "Rowan" wrote:
>>> >> >> >> >
>>> >> >> >> > > One way is like this:
>>> >> >> >> > >
>>> >> >> >> > > Sub InsRow()
>>> >> >> >> > >     With Range("A10")
>>> >> >> >> > >         If .Value = "991CX" Then
>>> >> >> >> > >             .EntireRow.Insert
>>> >> >> >> > >         End If
>>> >> >> >> > >     End With
>>> >> >> >> > > End Sub
>>> >> >> >> > >
>>> >> >> >> > > otherwise:
>>> >> >> >> > >
>>> >> >> >> > > Sub InsRow()
>>> >> >> >> > >     If Cells(10, 1).Value = "991CX" Then
>>> >> >> >> > >         Cells(10, 1).EntireRow.Insert
>>> >> >> >> > >     End If
>>> >> >> >> > > End Sub
>>> >> >> >> > >
>>> >> >> >> > > Hope this helps
>>> >> >> >> > > Rowan
>>> >> >> >> > >
>>> >> >> >> > >
>>> >> >> >> > > Hope this helps
>>> >> >> >> > > Rowan
>>> >> >> >> > >
>>> >> >> >> > > "Steve" wrote:
>>> >> >> >> > >
>>> >> >> >> > > > I would like to set up a macro to insert a blank row at
>>> >> >> >> > > > specific
>>> >> >> >> > > > spots in a
>>> >> >> >> > > > worksheet. For example, if cell a10 contains 991CX, i
>>> >> >> >> > > > would
>>> >> >> >> > > > like
>>> >> >> >> > > > to
>>> >> >> >> > > > have a
>>> >> >> >> > > > blank row inserted directly above it. Thanks in advance
>>> >> >> >> > > > for
>>> >> >> >> > > > any
>>> >> >> >> > > > help you are
>>> >> >> >> > > > able to offer.
>>> >> >> >> > > >
>>> >> >> >> > > > Steve
>>> >> >>
>>> >> >>
>>> >> >>
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>

----------


## Steve

it works great. Thanks to everyone that pitched in.

"STEVE BELL" wrote:

> Steve,
>
> I already sent you an email.
>
> But for anyone following this thread - the problem was empty cells in Column
> A.
> The previous code had a loop that was designed to end when it reached a
> blank cell, so... it hit
> a blank cell on the 3rd row and stopped.
>
> I changed the code to find the last used row in column A and used a For ....
> Next loop
> starting at the last row and using a step or -1.
>
> Now it works....
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "STEVE BELL" <AYNrand451@verizon.net> wrote in message
> news:y3THe.6882$4e6.3409@trnddc04...
> > Steve,
> >
> > Let's do this the hard way -
> > send a copy of your workbook or worksheet to my email
> > and I'll work with it until it works....
> >
> > rand451@verizon.net
> >
> > --
> > steveB
> >
> > Remove "AYN" from email to respond
> > "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
> > news:C6D54C1B-7AAA-4CC5-9B63-E26CB1A3083F@microsoft.com...
> >> I'm baffled. Nothing is happening. I am copying the info. Pasting it into
> >> a
> >> module. I then go back to the wksht and run the macro using the tools
> >> menu.
> >>
> >> "STEVE BELL" wrote:
> >>
> >>> Steve,
> >>>
> >>> My bad - left out an "&"
> >>> Also checked it out to see if it would run - and it did.
> >>> Also removed all tabs and excessive spaces to make copy & paste
> >>> better....
> >>>
> >>> Now try it out... And get back to us....
> >>>
> >>> Sub CheckRows()
> >>> Dim i As Long
> >>> i = 2
> >>> Do Until Trim(Cells(i, 1)) = ""
> >>> If Cells(i, 1) = "991CX" Then
> >>> MsgBox Cells(i, 1).Text & " " & Cells(i, 1).Address
> >>> Else
> >>> MsgBox Cells(i, 1).Address & " contains " & Cells(i, 1).Text & ".end of
> >>> text"
> >>> End If
> >>> i = i + 1
> >>> Loop
> >>> End Sub
> >>>
> >>> --
> >>> steveB
> >>>
> >>> Remove "AYN" from email to respond
> >>> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
> >>> news:35DA690F-370A-4A2A-AB0D-2D91CF438BB4@microsoft.com...
> >>> > Msgbox Cells(i, 1).Text & " " Cells(i,1).address
> >>> > This time i received a syntax error at the following line:
> >>> >
> >>> >
> >>> > "STEVE BELL" wrote:
> >>> >
> >>> >> Steve,
> >>> >>
> >>> >> No need to apologize!  We just aren't sure of what is happening.
> >>> >>
> >>> >> Open a new work book
> >>> >>
> >>> >> Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the
> >>> >> new
> >>> >> workbook.
> >>> >> Make sure at least one row in column A has 991CX
> >>> >> Put this code in a module and run it - you should get a message
> >>> >> for each row.
> >>> >>
> >>> >> Tell us what happens...
> >>> >>
> >>> >> Sub CheckRows()
> >>> >> Dim i As Long
> >>> >> i = 2
> >>> >> Do Until Trim(Cells(i, 1)) = ""
> >>> >>     If Cells(i, 1) = "991CX" Then
> >>> >>         Msgbox Cells(i, 1).Text & " " Cells(i,1).address
> >>> >>     Else
> >>> >>         Msgbox Cells(i,1).address & " contains " & cells(i,1).Text &
> >>> >> ".end
> >>> >> of text"
> >>> >>     End If
> >>> >>         i = i + 1
> >>> >> Loop
> >>> >> End Sub
> >>> >>
> >>> >> --
> >>> >> steveB
> >>> >>
> >>> >> Remove "AYN" from email to respond
> >>> >> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
> >>> >> news:0B93D775-4621-4CB8-B8DF-ABF703F14DFC@microsoft.com...
> >>> >> >I apologize guys. I am not trying to be a pain. Column A only has
> >>> >> >Certian
> >>> >> > Aircraft tail numbers in it. Column B:G is set up with different
> >>> >> > sets
> >>> >> > of
> >>> >> > information. I am not sure why. But i can't get this to run.
> >>> >> >
> >>> >> > "STEVE BELL" wrote:
> >>> >> >
> >>> >> >> Steve,
> >>> >> >>
> >>> >> >> Greg's code should work if any cell in column A contains (only)
> >>> >> >> 991CX
> >>> >> >> If there is more in the cell, than it won't work.
> >>> >> >>
> >>> >> >> Have you tried testing the code using the F8 key and stepping
> >>> >> >> through
> >>> >> >> it.
> >>> >> >> Set up a dummy sheet with some cells in column A = 991CX and see
> >>> >> >> what
> >>> >> >> happens.
> >>> >> >> (you only need a few rows).
> >>> >> >>
> >>> >> >> See if you can locate an error or something and get back to us.
> >>> >> >>
> >>> >> >> Sub InsertRows()
> >>> >> >> Dim i As Long
> >>> >> >> i = 2
> >>> >> >> Do Until Trim(Cells(i, 1)) = ""
> >>> >> >>     If Cells(i, 1) = "991CX" Then
> >>> >> >>         Cells(i, 1).EntireRow.Insert
> >>> >> >>         i = i + 2
> >>> >> >>     Else
> >>> >> >>         i = i + 1
> >>> >> >>     End If
> >>> >> >> Loop
> >>> >> >> End Sub
> >>> >> >>
> >>> >> >> --
> >>> >> >> steveB
> >>> >> >>
> >>> >> >> Remove "AYN" from email to respond
> >>> >> >> "Steve" <sgauer@neo.rr.com.nospamallowed> wrote in message
> >>> >> >> news:8ACF1BBA-4B3B-4B72-9C45-A5E5AA352AC9@microsoft.com...
> >>> >> >> > When I ran it it nothing happened.
> >>> >> >> >
> >>> >> >> > "Greg Wilson" wrote:
> >>> >> >> >
> >>> >> >> >> Have you tested mine? Mine does this.
> >>> >> >> >>
> >>> >> >> >> Regards,
> >>> >> >> >> Greg
> >>> >> >> >>
> >>> >> >> >> "Steve" wrote:
> >>> >> >> >>
> >>> >> >> >> > The value 991CX will not always be in a10. It will always be
> >>> >> >> >> > in
> >>> >> >> >> > column
> >>> >> >> >> > A.
> >>> >> >> >> > Just not always row 10. I am looking for something that will
> >>> >> >> >> > search
> >>> >> >> >> > col. A
> >>> >> >> >> > for 991CX, when found, insert a blank row above it. Sorry for
> >>> >> >> >> > the
> >>> >> >> >> > confusion
> >>> >> >> >> > guys.
> >>> >> >> >> >
> >>> >> >> >> > Steve
> >>> >> >> >> >
> >>> >> >> >> > "Rowan" wrote:
> >>> >> >> >> >
> >>> >> >> >> > > One way is like this:
> >>> >> >> >> > >
> >>> >> >> >> > > Sub InsRow()
> >>> >> >> >> > >     With Range("A10")
> >>> >> >> >> > >         If .Value = "991CX" Then
> >>> >> >> >> > >             .EntireRow.Insert
> >>> >> >> >> > >         End If
> >>> >> >> >> > >     End With
> >>> >> >> >> > > End Sub
> >>> >> >> >> > >
> >>> >> >> >> > > otherwise:
> >>> >> >> >> > >
> >>> >> >> >> > > Sub InsRow()
> >>> >> >> >> > >     If Cells(10, 1).Value = "991CX" Then
> >>> >> >> >> > >         Cells(10, 1).EntireRow.Insert
> >>> >> >> >> > >     End If
> >>> >> >> >> > > End Sub
> >>> >> >> >> > >
> >>> >> >> >> > > Hope this helps
> >>> >> >> >> > > Rowan
> >>> >> >> >> > >
> >>> >> >> >> > >
> >>> >> >> >> > > Hope this helps
> >>> >> >> >> > > Rowan
> >>> >> >> >> > >
> >>> >> >> >> > > "Steve" wrote:
> >>> >> >> >> > >
> >>> >> >> >> > > > I would like to set up a macro to insert a blank row at
> >>> >> >> >> > > > specific
> >>> >> >> >> > > > spots in a
> >>> >> >> >> > > > worksheet. For example, if cell a10 contains 991CX, i
> >>> >> >> >> > > > would
> >>> >> >> >> > > > like
> >>> >> >> >> > > > to
> >>> >> >> >> > > > have a
> >>> >> >> >> > > > blank row inserted directly above it. Thanks in advance
> >>> >> >> >> > > > for
> >>> >> >> >> > > > any
> >>> >> >> >> > > > help you are
> >>> >> >> >> > > > able to offer.
> >>> >> >> >> > > >
> >>> >> >> >> > > > Steve
> >>> >> >>
> >>> >> >>
> >>> >> >>
> >>> >>
> >>> >>
> >>> >>
> >>>
> >>>
> >>>
> >
> >
>
>
>

----------

