Reputation: 373
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
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