So here's a solution using code from here http://allenbrowne.com/func-09.html which may prove useful when getting the max or min values from a list. Any of my attempts to condense the code by creating a variable to pass the same list to the MaxOfList function as well as the InstrRev function was not successful, so 2 variables hold similar values. The finished code will be much smaller when all the unnecessary stuff is removed. The logic is
- get the max value of the array
- start from the right of the array values and find the right-most position of the max value.
- the Select Case block chooses the value for Text104 based on the position of the max value
Based on your posts, I understand that whenever a max value is repeated, its right-most position determines what goes in Text104.
Code:
Private Sub BuildArray()
Dim maxValue As Long
Dim strValues As String
'*****this block should not be required as the form controls can be referenced to get the values
'I needed it because I don't have the form or its controls
'Call to MaxOfList should be changed to refer to the form controls and not txt1, txt2, etc.
Dim txt1 As Long, txt2 As Long, txt3 As Long, txt4 As Long
'comment out all but one line for testing; the desired result to be passed to Text104
'txt1 = 1: txt2 = 2: txt3 = 3: txt4 = 4 'Na2CO3, HCl, HO3, Plain => Plain
'txt1 = 0: txt2 = 3: txt3 = 3: txt4 = 3 ' => Plain
'txt1 = 4: txt2 = 4: txt3 = 2: txt4 = 1 ' => HCl
txt1 = 3: txt2 = 0: txt3 = 3: txt4 = 1 ' => HO3
'******
'get the max value in the array
maxValue = MaxOfList(txt1, txt2, txt3, txt4)
strValues = txt1 & txt2 & txt3 & txt4
'find the right-most position of the max value and pass to a Select Case block
Select Case InStrRev(strValues, maxValue, -1)
Case Is = 4
MsgBox "Plain"
Case Is = 3
MsgBox "HO3"
Case Is = 2
MsgBox "HCl"
Case Else
MsgBox "Na2CO3"
End Select
End Sub
Code:
Function MaxOfList(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.
varMax = Null 'Initialize to null
For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next
MaxOfList = varMax
End Function
EDIT:
I see that I omitted part of the function. As long as I'm fixing my cut and paste screw-up, I thought I'd post what the short version of the sub should look like (where the form control values are assigned to variables).
Code:
Private Sub BuildArray()
Dim maxValue As Long
Dim strValues As String
txt1 = Me.Textbotx1
txt2 = Me.Textbotx2
txt3 = Me.Textbotx3
txt4 = Me.Textbotx4
maxValue = MaxOfList(txt1, txt2, txt3, txt4)
strValues = txt1 & txt2 & txt3 & txt4
Select Case InStrRev(strValues, maxValue, -1)
Case Is = 4
MsgBox "Plain"
Case Is = 3
MsgBox "HO3"
Case Is = 2
MsgBox "HCl"
Case Else
MsgBox "Na2CO3"
End Select
End Sub