Find Highest Value Based on Condition

Hi FallingDown,
try this

Sub ertert()
Dim sn&, x, i&
sn = Application.InputBox("Enter the sector number", , 510, Type:=1)
If sn = 0 Then Exit Sub

x = Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value
For i = UBound(x) To 2 Step -1
    If x(i, 1) = sn Then
        If x(i, 3) = "NORM" Then
            Cells(i, 4) = "Result: " & x(i, 2) + 1: Exit For
        End If
    End If
Next i

If i = 1 Then Cells(Rows.Count, 1).End(xlUp)(2, 1).Resize(, 4).Value = _
        Array(sn, sn * 1000 + 1, "NORM", "Result: " & sn * 1000 + 1)
End Sub

HI nilem

It's not quite what I am looking for

However, I got something in the meanwhile

Sub NextNum()
Dim LR As Long, cell As Range, rng As Range
s = InputBox("Enter Sector")


    For Each cell In Range("a2:c30")
        If cell.Value = s Then
        cell.Rows.Select
            If rng Is Nothing Then
                Set rng = cell
            Else
                If InStr(cell.Offset(0, 2).Value, "NORM") Then Set rng = Union(rng, cell)
            End If
        End If
    Next cell
    
    rng.EntireRow.Select
    MsgBox (Application.WorksheetFunction.Max(rng.EntireRow)) + 1



End Sub

I did not specify in my original request, and in the sample as well, that apart from NORM there are other Categories as well
the problem is that my code only looks at value with NORM. What it should do is IGNORE values containing *SPEC*.

can I use the InStr function to NOT include the value specified?

Hi everyone and happy new year

Anybody an idesa how I can use instr (or something else) to not include a certain string?

Hi FallingDown,
try to replace this

If InStr(cell.Offset(0, 2).Value, "NORM") Then Set rng = Union(rng, cell)

with this

If InStr(cell.Offset(0, 2).Value, "SPEC") = 0 Then Set rng = Union(rng, cell)

Thanks, that did it!