Selection a row while within a "FOR Each"

  • Thread starter Thread starter David Schrader
  • Start date Start date
D

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
 
try this
Cell_In_Loop.EntireRow.Insert Shift:=xlDown

instead of

Rows(Cell_In_Loop.Offset(0, 0)).Select
Selection.Insert Shift:=xlDown
 
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
 
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)
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A question relating to "for each cell_in_loop in range" 3
Format based on cell value 5
Eliminate blanks while copy row and transpose 11
Hide / Unhide each row from StartRow to LastRow for multiple conditions 6
loop in range 3
Border formatting row of cells 22
Problem w/ Do While - For Each 3
VBA CreateNames (ignores last 5 rows?) 6

Back
Top