Selection a row while within a "FOR Each"
 

Hi all,

I try to avoid cluttering this group with messages - waiting
until I've tried everything I can and have exhausted every-
thing I can think of. Well, I'm there now. So, right to the
point to avoid any excess bandwidth.

I have a spreadsheet containing columns of data. Row one has
column titles. Column "E" contains a "Date" (custom) formatted
as "ddd | yy/mm/dd" [not that it matters as far as this question
is concerned]. The number of rows may vary from 50 to 2000 but
the (row) last cell in column A will contain "EOList."

So far I have developed multiple routines with:

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
' Whatever
Exit For
Else
' Whatever
End If
Next

This has worked fine until now... . What I need to do now is
insert a blank line each time the day changes. I haven't had
any problem changing the color of the cell's text or it's
interior color. I've been able to pick out the day of the
week easily using the weekday function on the date which is
stored in the cell.

The problem arises when I try to "select" the row (when the
day changes) and insert a blank row before that row. Excel
says an object has to be selected/declared. Here's what
I've tried. (I started small just trying to put in a blank row
before the "EOList" row.)

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
Rows(Cell_In_Loop.Offset(0, 0)).Select
Selection.Insert Shift:=xlDown
Exit For
Else
' Whatever
End If
Next

Suggestions/Solutions?

David

Selection a row while within a "FOR Each"
 

try this
Cell_In_Loop.EntireRow.Insert Shift:=xlDown

instead of

Rows(Cell_In_Loop.Offset(0, 0)).Select
Selection.Insert Shift:=xlDown

--


Gary


"David Schrader" wrote in message
...
Hi all,

I try to avoid cluttering this group with messages - waiting
until I've tried everything I can and have exhausted every-
thing I can think of. Well, I'm there now. So, right to the
point to avoid any excess bandwidth.

I have a spreadsheet containing columns of data. Row one has
column titles. Column "E" contains a "Date" (custom) formatted
as "ddd | yy/mm/dd" [not that it matters as far as this question
is concerned]. The number of rows may vary from 50 to 2000 but
the (row) last cell in column A will contain "EOList."

So far I have developed multiple routines with:

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
' Whatever
Exit For
Else
' Whatever
End If
Next

This has worked fine until now... . What I need to do now is
insert a blank line each time the day changes. I haven't had
any problem changing the color of the cell's text or it's
interior color. I've been able to pick out the day of the
week easily using the weekday function on the date which is
stored in the cell.

The problem arises when I try to "select" the row (when the
day changes) and insert a blank row before that row. Excel
says an object has to be selected/declared. Here's what
I've tried. (I started small just trying to put in a blank row
before the "EOList" row.)

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
Rows(Cell_In_Loop.Offset(0, 0)).Select
Selection.Insert Shift:=xlDown
Exit For
Else
' Whatever
End If
Next

Suggestions/Solutions?

David

Selection a row while within a "FOR Each"
 

David,

Dim Cell_In_Loop As Range

For Each Cell_In_Loop In Range("E3:E2000")
If Cells(Cell_In_Loop.Row, 1) = "EOList" Then
Cell_In_Loop.Offset(1, 0).EntireRow.Insert Shift:=xlDown
Exit For
Else
' Whatever
End If
Next

hth,

Doug

"David Schrader" wrote in message
...
Hi all,

I try to avoid cluttering this group with messages - waiting
until I've tried everything I can and have exhausted every-
thing I can think of. Well, I'm there now. So, right to the
point to avoid any excess bandwidth.

I have a spreadsheet containing columns of data. Row one has
column titles. Column "E" contains a "Date" (custom) formatted
as "ddd | yy/mm/dd" [not that it matters as far as this question
is concerned]. The number of rows may vary from 50 to 2000 but
the (row) last cell in column A will contain "EOList."

So far I have developed multiple routines with:

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
' Whatever
Exit For
Else
' Whatever
End If
Next

This has worked fine until now... . What I need to do now is
insert a blank line each time the day changes. I haven't had
any problem changing the color of the cell's text or it's
interior color. I've been able to pick out the day of the
week easily using the weekday function on the date which is
stored in the cell.

The problem arises when I try to "select" the row (when the
day changes) and insert a blank row before that row. Excel
says an object has to be selected/declared. Here's what
I've tried. (I started small just trying to put in a blank row
before the "EOList" row.)

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
Rows(Cell_In_Loop.Offset(0, 0)).Select
Selection.Insert Shift:=xlDown
Exit For
Else
' Whatever
End If
Next

Suggestions/Solutions?

David

Selection a row while within a "FOR Each"
 

Sub AddLines()
Dim iLastRow As Long
Dim i As Long

On Error Resume Next
iLastRow = Application.Match("EOList", Columns(1), 0)
On Error GoTo 0
If iLastRow < 0 Then
For i = iLastRow To 3 Step -1
If Cells(i, "E").Value < Cells(i - 1, "E").Value Then
Rows(i).Insert
End If
Next i
End If

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"David Schrader" wrote in message
...
Hi all,

I try to avoid cluttering this group with messages - waiting
until I've tried everything I can and have exhausted every-
thing I can think of. Well, I'm there now. So, right to the
point to avoid any excess bandwidth.

I have a spreadsheet containing columns of data. Row one has
column titles. Column "E" contains a "Date" (custom) formatted
as "ddd | yy/mm/dd" [not that it matters as far as this question
is concerned]. The number of rows may vary from 50 to 2000 but
the (row) last cell in column A will contain "EOList."

So far I have developed multiple routines with:

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
' Whatever
Exit For
Else
' Whatever
End If
Next

This has worked fine until now... . What I need to do now is
insert a blank line each time the day changes. I haven't had
any problem changing the color of the cell's text or it's
interior color. I've been able to pick out the day of the
week easily using the weekday function on the date which is
stored in the cell.

The problem arises when I try to "select" the row (when the
day changes) and insert a blank row before that row. Excel
says an object has to be selected/declared. Here's what
I've tried. (I started small just trying to put in a blank row
before the "EOList" row.)

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
Rows(Cell_In_Loop.Offset(0, 0)).Select
Selection.Insert Shift:=xlDown
Exit For
Else
' Whatever
End If
Next

Suggestions/Solutions?

David

Selection a row while within a "FOR Each"
 

Bob,

Thanks for the reply.

I'm not certain I follow the logic here - at least as it fits
into my question with regard to the "FOR Each..." loop
question - but I'll check it out and see if it does what I'm
looking for.

David

"Bob Phillips" wrote in message
...
Sub AddLines()
Dim iLastRow As Long
Dim i As Long

On Error Resume Next
iLastRow = Application.Match("EOList", Columns(1), 0)
On Error GoTo 0
If iLastRow < 0 Then
For i = iLastRow To 3 Step -1
If Cells(i, "E").Value < Cells(i - 1, "E").Value Then
Rows(i).Insert
End If
Next i
End If

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"David Schrader" wrote in message
...
Hi all,

I try to avoid cluttering this group with messages - waiting
until I've tried everything I can and have exhausted every-
thing I can think of. Well, I'm there now. So, right to the
point to avoid any excess bandwidth.

I have a spreadsheet containing columns of data. Row one has
column titles. Column "E" contains a "Date" (custom) formatted
as "ddd | yy/mm/dd" [not that it matters as far as this question
is concerned]. The number of rows may vary from 50 to 2000 but
the (row) last cell in column A will contain "EOList."

So far I have developed multiple routines with:

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
' Whatever
Exit For
Else
' Whatever
End If
Next

This has worked fine until now... . What I need to do now is
insert a blank line each time the day changes. I haven't had
any problem changing the color of the cell's text or it's
interior color. I've been able to pick out the day of the
week easily using the weekday function on the date which is
stored in the cell.

The problem arises when I try to "select" the row (when the
day changes) and insert a blank row before that row. Excel
says an object has to be selected/declared. Here's what
I've tried. (I started small just trying to put in a blank row
before the "EOList" row.)

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
Rows(Cell_In_Loop.Offset(0, 0)).Select
Selection.Insert Shift:=xlDown
Exit For
Else
' Whatever
End If
Next

Suggestions/Solutions?

David

Selection a row while within a "FOR Each"
 

Doug,

Not exactly the Excel "logic" I expected but I'll
give it a try and let you know asap whether it
works or not in producing what I need.

I'm assuming that all I'd have to do is insert an
equivalent line into the else to do the same
when the day changed... , and then just update
the day to be checked... . Should be a snap.

Many thanks.

David

"Doug Glancy" wrote in message
...
David,

Dim Cell_In_Loop As Range

For Each Cell_In_Loop In Range("E3:E2000")
If Cells(Cell_In_Loop.Row, 1) = "EOList" Then
Cell_In_Loop.Offset(1, 0).EntireRow.Insert Shift:=xlDown
Exit For
Else
' Whatever
End If
Next

hth,

Doug

"David Schrader" wrote in message
...
Hi all,

I try to avoid cluttering this group with messages - waiting
until I've tried everything I can and have exhausted every-
thing I can think of. Well, I'm there now. So, right to the
point to avoid any excess bandwidth.

I have a spreadsheet containing columns of data. Row one has
column titles. Column "E" contains a "Date" (custom) formatted
as "ddd | yy/mm/dd" [not that it matters as far as this question
is concerned]. The number of rows may vary from 50 to 2000 but
the (row) last cell in column A will contain "EOList."

So far I have developed multiple routines with:

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
' Whatever
Exit For
Else
' Whatever
End If
Next

This has worked fine until now... . What I need to do now is
insert a blank line each time the day changes. I haven't had
any problem changing the color of the cell's text or it's
interior color. I've been able to pick out the day of the
week easily using the weekday function on the date which is
stored in the cell.

The problem arises when I try to "select" the row (when the
day changes) and insert a blank row before that row. Excel
says an object has to be selected/declared. Here's what
I've tried. (I started small just trying to put in a blank row
before the "EOList" row.)

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
Rows(Cell_In_Loop.Offset(0, 0)).Select
Selection.Insert Shift:=xlDown
Exit For
Else
' Whatever
End If
Next

Suggestions/Solutions?

David

Selection a row while within a "FOR Each"
 

Doug,

Bit of a delay while I tried it. It worked - more-or-less
as you write it and not exactly as I wanted but after
dinking with it a bit I got it to work in my simplest
case just as I had hoped.

I'm still having problems with the more advanced
case where the day changes since the logic and
the FOR Loop don't really interact smoothly with
the newly inserted (blank) line (and it keeps want-
ing to add blank lines at the same place). As soon
as I get the logic clear/cleaned-up everything
should work fine.

Somehow the "xlDOWN" isn't really doing/working
as I would have thought it would in the given
situation.

David

"David Schrader" wrote in message
...
Doug,

Not exactly the Excel "logic" I expected but I'll
give it a try and let you know asap whether it
works or not in producing what I need.

I'm assuming that all I'd have to do is insert an
equivalent line into the else to do the same
when the day changed... , and then just update
the day to be checked... . Should be a snap.

Many thanks.

David

"Doug Glancy" wrote in message
...
David,

Dim Cell_In_Loop As Range

For Each Cell_In_Loop In Range("E3:E2000")
If Cells(Cell_In_Loop.Row, 1) = "EOList" Then
Cell_In_Loop.Offset(1, 0).EntireRow.Insert Shift:=xlDown
Exit For
Else
' Whatever
End If
Next

hth,

Doug

"David Schrader" wrote in message
...
Hi all,

I try to avoid cluttering this group with messages - waiting
until I've tried everything I can and have exhausted every-
thing I can think of. Well, I'm there now. So, right to the
point to avoid any excess bandwidth.

I have a spreadsheet containing columns of data. Row one has
column titles. Column "E" contains a "Date" (custom) formatted
as "ddd | yy/mm/dd" [not that it matters as far as this question
is concerned]. The number of rows may vary from 50 to 2000 but
the (row) last cell in column A will contain "EOList."

So far I have developed multiple routines with:

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
' Whatever
Exit For
Else
' Whatever
End If
Next

This has worked fine until now... . What I need to do now is
insert a blank line each time the day changes. I haven't had
any problem changing the color of the cell's text or it's
interior color. I've been able to pick out the day of the
week easily using the weekday function on the date which is
stored in the cell.

The problem arises when I try to "select" the row (when the
day changes) and insert a blank row before that row. Excel
says an object has to be selected/declared. Here's what
I've tried. (I started small just trying to put in a blank row
before the "EOList" row.)

FOR Each Cell_In_Loop In Range("CELL2:CELL2000")
IF ( Cell_In_Loop.Offset(0, Whatever to get Cell A).Value _
= "EOList" ) Then
Rows(Cell_In_Loop.Offset(0, 0)).Select
Selection.Insert Shift:=xlDown
Exit For
Else
' Whatever
End If
Next

Suggestions/Solutions?

David