Show 2 rows of data, if one cell 0

[PDuPre']

Hello, appreciate any help,

I have an error log that prints out two rows of data seperated by a blank row for each error. The error log prints out each month. There may be several hundred errors, but all but a few have a value of zero and are irrelevant.

The first row of each error has 7 cells of data. The second row has 8 cells of data. The 6th cell (column) on the second row is the value. If it does not equal zero then I'd like to see that error.

Looking for some VBA code to take each valid error and write it to another worksheet. Ideally I will initiate the macro via a button or other trigger and have the valid errors print out on the page below. Will repeat periodically each month. No need to keep a record of prior valid errors.

I have a macro that can open the comma delimited text file, format it, copy it and apply a formula to identify the columns with the valid errors, but I don't know how to write the 2 rows of data to the second worksheet.

Thank for any help,

Philip

[Nimrod]

If row 8 ( for example) has errorCode>0 do you want both Row 7 and Row 8 copied to second sheet or just Row 8 ?

[PDuPre']

On 2002-05-13 12:13, Nimrod wrote:
If row 8 ( for example) has errorCode>0 do you want both Row 7 and Row 8 copied to second sheet or just Row 8 ?

I would want both row 7 and row 8 ideally.

Philip

[bergy]

[/quote]
I would want both row 7 and row 8 ideally.

Philip
[/quote]

The code below only takes the 2 rows, but only the first 8 cells. It does not copy the entire row.

Sub test()

ErrorLog = ActiveSheet.Name
LastRow = Cells(65536, 1).End(xlUp).Row
Col_A = 1
Col_F = 6
Col_H = 8
RowNum = 1 'Adjust for you start row

ErrorLog = ActiveSheet.Name
While RowNum < LastRow
If Cells(RowNum + 1, Col_F).Value <> 0 Then
Range(Cells(RowNum, Col_A), Cells(RowNum + 1, Col_H)).Select
Selection.Copy
Sheets("Sheet2").Select
If (Cells(1, 1).Value) = "" Then
Cells(1, 1).Select
Else
Cells(65536, 1).End(xlUp).Offset(2, 0).Select
End If
ActiveSheet.Paste
Sheets(ErrorLog).Select
End If
RowNum = RowNum + 3
Wend
End Sub


Hope it works!

[bergy]

Oops! Forgot to mention you would need to mofify the sheet names.

[Nimrod]

One last question:
If Row 8 Col 6 has your error value what does Row7 col6 have ? In other words is it a number or text ? It would make the solution easier if C7 and C8 had different data types.

[PDuPre']

On 2002-05-13 12:31, Nimrod wrote:
One last question:
If Row 8 Col 6 has your error value what does Row7 col6 have ? In other words is it a number or text ? It would make the solution easier if C7 and C8 had different data types.

Row 7 column 6 is text. It contains the word "In" every time.

Philip

[ This Message was edited by: PDuPre' on 2002-05-13 13:11 ]

[PDuPre']

On 2002-05-13 12:30, bergy wrote:
Hope it works!

I'll try it. Thanks for the quick response. MS-Word locked up half my machine, so I'll reboot and give it a try.
Nimrod, if you have a different way to do this, I'd love to see it. Trying to learn as much as I can.

Thanks!

Philip

[PDuPre']

It works. Took me a bit to get it setup, but it copied over everything I wanted and nothing I didn't.

Thanks!

Philip

[Nimrod]

Oh well to late ... well here it is anyway.

Sub CopyErrorLog()
    SourceSheet = "ErrorLog"
    TargetSheet = "ErrorSummary"
    TargRow = 1
    EvalCol = 6

For RwCnt = 2 To 65536 Step 2
    If Val(Sheets(SourceSheet).Cells(RwCnt, EvalCol).Value) > 0 Then ' copy and paste
        Worksheets(SourceSheet).Range(RwCnt - 1 & ":" & RwCnt).Copy
        Sheets("ErrorSummary").Range(TargRow & ":" & TargRow + 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
        TargRow = TargRow + 2
    End If
    If Len(Trim(Sheets(SourceSheet).Cells(RwCnt, 1))) = 0 Then Exit Sub
Next
End Sub