Look at last 7 rows in worksheets, put results to sheet 1

**xmmx** · 06-21-2011, 05:50 PM

I have a macro that will output a negative value if something is wrong with a certain row. What I want to do now is make a macro that will scan sheet 2 thru the end for the last 7 rows with data, and count negative values. If 4 of the 7 have negative values, it goes into Sheet 1 and puts an x next to the sheet name.

Logic:
hit button on sheet 1 -> go to Sheet 2 - count negative values in last 7 rows on column K (11) -> if more than 4, go to first sheet to the sheet name (column J) -> go right 2 -> put an X -> go to sheet 3

I tried my hand at writing some of the code, but I'm still very new at excel...

'loop thru all the worksheets, selecting the last 7 values in column J and defining that as a range. in the code below, I try to do stuff with the range but I get very lost there.

Sub count()
Dim wsheet As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
For Each wsheet In wb.Worksheets
    With wsheet
        If wsheet.Name <> "Sheet1" Then
            wsheet.Activate
            With ActiveSheet
                Set rng = Range("J65536").End(xlUp).Offset(-7, 0)
            End With
        End If
    End With
Next

End Sub

'not sure how to implement this part about writing to the first sheet