| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "Center across selection" rather than "Merge cells" | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Icon for "Wrap" and for "Centre Across Selection" | Excel Worksheet Functions | |||
Hybrid Mode
