Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Looping through a file
Hi,
I have tried to make some code that will search through a file and move rows based on certain criteria to new spreadsheets. Code as follows: Sub life_file() If Range("x1").Value Or Range("ae1").Value Or Range("ah1").Value 0 Then ActiveCell.EntireRow.Copy Sheets("LIFE").Select ActiveSheet.Paste Sheets("EOI_TEST").Select ElseIf Range("aa1").Value Or Range("ac1").Value 0 Then ActiveCell.EntireRow.Copy Sheets("LTD_STD").Select ActiveSheet.Paste ElseIf Range("x1").Value And Range("ae1").Value And Range("ah1").Value And _ Range("aa1").Value And Range("ac1").Value 0 Then ActiveCell.EntireRow.Copy Sheets("LIFE").Select ActiveSheet.Paste Sheets("LTD_STD").Select ActiveSheet.Paste End If End Sub The problem is that I can only make it look at the first row on my file. There could be a varying number of rows on the file so i can't just sent a specific range for the macro to look at. I'm not sure how to make it loop through until all rows have been looked at. Can some one help? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Looping through a file
Is this what you want?
Sub life_file() Dim LastRow As Long Dim i As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "X").End(xlUp).Row For i = 1 To LastRow If .Cells(i, "X").Value 0 Or _ .Cells(i, "AE").Value 0 Or _ .Cells(i, "AH").Value 0 Then .Rows(i).Copy Sheets("LIFE").Range("A1") ElseIf .Cells(i, "AA").Value 0 Or _ .Cells(i, "AC").Value 0 Then .Rows(i).Copy Sheets("LTD_STD").Range("A1") ElseIf .Cells(i, "X").Value 0 And _ .Cells(i, "AE").Value 0 And _ .Cells(i, "AH").Value 0 And _ .Cells(i, "AA1").Value 0 And _ .Cells(i, "AC").Value 0 Then .Rows(i).Copy Sheets("LIFE").Range("A1") .Rows(i).Copy Sheets("LTD_STD").Range("A1") End If Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dagonini" wrote in message ... Hi, I have tried to make some code that will search through a file and move rows based on certain criteria to new spreadsheets. Code as follows: Sub life_file() If Range("x1").Value Or Range("ae1").Value Or Range("ah1").Value 0 Then ActiveCell.EntireRow.Copy Sheets("LIFE").Select ActiveSheet.Paste Sheets("EOI_TEST").Select ElseIf Range("aa1").Value Or Range("ac1").Value 0 Then ActiveCell.EntireRow.Copy Sheets("LTD_STD").Select ActiveSheet.Paste ElseIf Range("x1").Value And Range("ae1").Value And Range("ah1").Value And _ Range("aa1").Value And Range("ac1").Value 0 Then ActiveCell.EntireRow.Copy Sheets("LIFE").Select ActiveSheet.Paste Sheets("LTD_STD").Select ActiveSheet.Paste End If End Sub The problem is that I can only make it look at the first row on my file. There could be a varying number of rows on the file so i can't just sent a specific range for the macro to look at. I'm not sure how to make it loop through until all rows have been looked at. Can some one help? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Looping through a file
I'm not sure I understand what you mean...do I need to qualifiy the
range? Gah, I'm such a noob at this and am trying to pick it up on the fly. I put in the 0 on each range. But it is only moving the last row on the file and is not moving the other rows. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Looping through a file
Looks like Bob gave you a good answer to work with.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Dagonini" wrote in message ... I'm not sure I understand what you mean...do I need to qualifiy the range? Gah, I'm such a noob at this and am trying to pick it up on the fly. I put in the 0 on each range. But it is only moving the last row on the file and is not moving the other rows. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Looping through a file
When I try using Bob's it still only pastes the last row on the file
into a new spreadsheet. I |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Looping through a file
If desired, send your workbook to my address below along with snippets of
these msgs, a clear explanation of what you want and examples of before/after -- Don Guillett Microsoft MVP Excel SalesAid Software "Dagonini" wrote in message ... When I try using Bob's it still only pastes the last row on the file into a new spreadsheet. I |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Looping through a file
She said the numbers in Columns X, AE, AH are Life volume coverages. If any one of those fields is filled in with a volume then I need the record to be copied to the LIFE spreadsheet. For example in the first record on the test file column X has 210000. that means that the entire row needs to be copied to the LIFE spreadsheet. That row also has 60 in column AA (which is STD) so it needs to also be moved to the LTD_STD spreadsheet. The second record has X empty but there is 55000 in column AE so it also needs to be moved to the LIFE spreadsheet. There is also a 60 in column AC so the record needs to go to the LTD_STD spreadsheet. I sent this to copy to one or both as indicated from above x,ae,ah to life aa,ac to the other if both, then both Sub life_fileDON() Dim LastRow As Long Dim i As Long 'clear destination rows rowlife = Sheets("LIFE").Cells(Rows.Count, 1).End(xlUp).Row + 1 Sheets("LIFE").Rows("1:" & rowlife).ClearContents rowltd = Sheets("LTD_STD").Cells(Rows.Count, 1).End(xlUp).Row + 1 Sheets("LTD_STD").Rows("1:" & rowltd).ClearContents With Sheet1 LastRow = .Cells(.Rows.Count, "X").End(xlUp).Row For i = 1 To LastRow rowlife = Sheets("LIFE").Cells(Rows.Count, 1).End(xlUp).Row + 1 rowltd = Sheets("LTD_STD").Cells(Rows.Count, 1).End(xlUp).Row + 1 If .Cells(i, "X").Value 0 Or _ .Cells(i, "AE").Value 0 Or _ .Cells(i, "AH").Value 0 Then .Rows(i).Copy Sheets("LIFE").Cells(rowlife, 1) End If If .Cells(i, "AA").Value 0 Or _ .Cells(i, "AC").Value 0 Then .Rows(i).Copy Sheets("LTD_STD").Cells(rowltd, 1) End If Next i End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... If desired, send your workbook to my address below along with snippets of these msgs, a clear explanation of what you want and examples of before/after -- Don Guillett Microsoft MVP Excel SalesAid Software "Dagonini" wrote in message ... When I try using Bob's it still only pastes the last row on the file into a new spreadsheet. I |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
About my looping | Excel Programming | |||
Looping through folders and using the first file in each folder | Excel Programming | |||
Looping | Excel Programming | |||
Looping | Excel Programming | |||
Looping though *.xls files except for the main consolidation file | Excel Programming |