count if not 2-8, column by name

do you mean 2-8  as a text or between 2 to 8 any number from 2 to 8?

sorry  - the numbers 2,3,4,5,6,7, or 8. I need to count lines that are NOT those numbers.

This macro will find Zone. It could easily be turned into a UDF with any other code you need when you find the zone.

Sub FindZone()
Dim lngCol As Long

For lngCol = 1 To Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
    If UCase(Cells(1, lngCol)) = "ZONE" Then
        MsgBox "Zone is column " & lngCol
        Exit For
    End If
Next
End Sub

Select allOpen in new window

Here's the UDF.

Usage:
GetZoneCount()

Function GetZoneCount() As Integer
Dim lngCol As Long
Dim lngRow As Long

For lngCol = 1 To Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
    If UCase(Cells(1, lngCol)) = "ZONE" Then
        Exit For
    End If
Next
For lngRow = 2 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Cells(lngRow, lngCol).Value < 2 Or Cells(lngRow, lngCol).Value > 8 Then
        GetZoneCount = GetZoneCount + 1
    End If
Next

End Function

Select allOpen in new window

Martin Liss, I don't know how to use that UDF. I need to have the result in a cell.

Here's how you add the UDF.

In Excel, Press Alt+F11 to open Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu

Copy the UDF (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

After that you can use it just like a normal formula.

Euro5

did you check my attachment? is there any problem with that?

Perfect thanks!!