.find two criteria

[Fer88]

I need help please i´m trying to use .find with two criteria true the code below but only Works for the first result, i mean need to find the row where the code (criteria1-column a) "código" and the center(criteria2-column b) "centro" matches then to bring me the offset value from colum e, but my code only Works for the first result from .find(codigo), else it return "0".

Function existencia1CBE(codigo As Variant, centro As Variant)
With Worksheets("1CBE").Range("a2: a100000")
Set midato = .Find(codigo, _
            LookAt:=xlWhole, SearchOrder:=xlByColumns, _
            SearchDirection:=xlNext, MatchCase:=False)
    If Not midato Is Nothing Then
    firstAddress = midato.Address
        Do Until midato Is Nothing
Line1:
                If midato.Offset(0, 1).Value = centro Then
                existencia1CBE = midato.Offset(0, 4).Value
                Exit Do
                Else
                Set midato = .FindNext(midato)
                If Not midato Is Nothing Then
                GoTo Line1
                End If
                If midato.Address = firstAddress Then
                Exit Do
                GoTo Line1
                End If
                     End If
        Loop
End If
End With
End Function

 

[ParamRay]

.
.

Try a function like this instead:

Function ExistencialCBE(Codigo, Centro)

    Dim LRow As Long
    Dim SRng As Range
    Dim Cell As Range
    
    With ThisWorkbook.Worksheets("1CBE")
        LRow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set SRng = .Range("A2: A" & LRow)
    End With
    
    For Each Cell In SRng
        If LCase(Cell.Value) = LCase(Codigo) Then
            If LCase(Cell.Offset(0, 1).Value) = LCase(Centro) Then
                ExistencialCBE = Cell.Offset(0, 4).Value
                Exit Function
            End If
        End If
    Next Cell
    
End Function

 

[Fer88]

OMG you are the greatest!! Thank you very much!

.
.

Try a function like this instead:

Function ExistencialCBE(Codigo, Centro)

    Dim LRow As Long
    Dim SRng As Range
    Dim Cell As Range
    
    With ThisWorkbook.Worksheets("1CBE")
        LRow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set SRng = .Range("A2: A" & LRow)
    End With
    
    For Each Cell In SRng
        If LCase(Cell.Value) = LCase(Codigo) Then
            If LCase(Cell.Offset(0, 1).Value) = LCase(Centro) Then
                ExistencialCBE = Cell.Offset(0, 4).Value
                Exit Function
            End If
        End If
    Next Cell
    
End Function