Need Help Looping through a file

[Dagonini]

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!

 

[Bob Phillips]

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)

 

[Don Guillett]

What is the name of your source sheet("EOI_TEST")?
Where on the destination sheet(s) do you want the data to go
You do NOT need to select
You DO need to qualify each in your OR
If Range("x1").Value>0 Or Range("ae1").Value>0 Or Range("ah1").Value > 0

 

[Dagonini]

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.

 

[Don Guillett]

Looks like Bob gave you a good answer to work with.

 

[Dagonini]

When I try using Bob's it still only pastes the last row on the file
into a new spreadsheet. I

 

[Don Guillett]

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]

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