Johnny Abreu
Johnny Abreu

Reputation: 373

Min value while ignoring zeros

Is there another way to get the smallest values, while ignoring the 0s, out of the Instr i show in the code below? I tried to use If a couple of times in order to get the smallest value but it does not work everytime because if the first two lines (letters) are empty or equals to 0 the rest of the If will always be 0. I also tried to use Application.WorksheetFunction.Min but didn't manage to find a way in vba to include a condition to ignore the 0s.

    If carater = "d" Then
    str_f = str_f & str_var
        b = InStr(1, Mid(cel, 1, 999), "/", vbTextCompare) 'cel = (dAA11b+dAA12b)/dAA13b*100
        c = InStr(1, Mid(cel, 1, 999), "*", vbTextCompare)
        d = InStr(1, Mid(cel, 1, 999), "-", vbTextCompare)
        e = InStr(1, Mid(cel, 1, 999), "+", vbTextCompare)
        f = InStr(1, Mid(cel, 1, 999), ")", vbTextCompare)

        minimo = 0
        If b <> 0 Then
        minimo = b
        End If

        'test = Application.WorksheetFunction.Min

        If c <> 0 And c < minimo Then
        minimo = c
        End If

        If d <> 0 And d < minimo Then
        minimo = d
        End If

        If e <> 0 And e < minimo Then
        minimo = e
        End If

        If f <> 0 And f < minimo Then
        minimo = f
        End If

        'a = Mid(cel, i, minimo - 2)
        'str_f = str_f & a & str_var1
        '.Cells(567, 3).Value = str_f

    End If

Upvotes: 0

Views: 459

Answers (1)

Ambie
Ambie

Reputation: 4977

I'm not sure what you're trying to do here, but it looks as if you're trying to find the first occurrence of a character that exists in a list. A common way of doing this is to iterate each character in your string and test if it exists within the list. As soon as you've found a match, you can exit the routine with the found index value. Code would look something like this:

Option Explicit

Public Sub RunMe()
    Dim result As Long

    result = firstOccurence(Sheet1.Range("A1"))

    If result = 0 Then
        MsgBox "No operator found."
    Else
        MsgBox "Operator found at " & result
    End If

End Sub

Private Function firstOccurence( _
    cellToTest As Range, _
    Optional oprSet As String = "/*-+)") As Long

    Dim chrSet As String, chr As String, opr As String
    Dim c As Long, o As Long

    'You should sense check the parameters here.

    ' ...

    'Test if each character of the string to be tested
    'exists in the operator list.
    'As soon as one is found, exit the function.
    chrSet = cellToTest.Value2

    For c = 1 To Len(chrSet) 'the test string
        chr = Mid(chrSet, c, 1)
        For o = 1 To Len(oprSet) 'the operator list
            opr = Mid(oprSet, o, 1)
            If chr = opr Then 'found one!
                firstOccurence = c
                Exit Function
            End If
        Next
    Next

    'Nothing found so function returns 0.

End Function

Upvotes: 1

Related Questions