Selection a row while within a "FOR Each"

[David Schrader]

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

[Doug Glancy]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>

[Gary Keramidas]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>

[David Schrader]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>>
>
>

[David Schrader]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>>
>
>

[David Schrader]

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" <[email protected]> wrote in message
news:epk%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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
>>>
>>>
>>
>>
>
>