Find Largest Value From 10 S’abonner

0
Avatar
Legacy Poster

I am new to VB scripting and Bartender but have developing label designs for the last few months. I've come across a bit of a sticking point now though.

 

I'm relying on this multi-line script to find the highest 'Saving' (calculated sub-string) from the 10 being sent to the label.

 

If (Saving1)>(Saving2) AND  (Saving1)>(Saving3) AND  (Saving1)>(Saving4) AND  (Saving1)>(Saving5) AND  (Saving1)>(Saving6) AND  (Saving1)>(Saving7) AND  (Saving1)>(Saving8) AND  (Saving1)>(Saving9) AND  (Saving1)>(Saving10) Then
    Value = (Saving1)
Else If (Saving2)>(Saving3) AND  (Saving2)>(Saving4) AND  (Saving2)>(Saving5) AND  (Saving2)>(Saving6) AND  (Saving2)>(Saving7) AND  (Saving2)>(Saving8) AND  (Saving2)>(Saving9) AND  (Saving2)>(Saving10) Then
    Value = (Saving2)
Else If (Saving3)>(Saving4) AND  (Saving3)>(Saving5) AND  (Saving3)>(Saving6) AND  (Saving3)>(Saving7) AND  (Saving3)>(Saving8) AND  (Saving3)>(Saving9) AND  (Saving3)>(Saving10) Then
    Value = (Saving3)
Else If (Saving4)>(Saving5) AND  (Saving4)>(Saving6) AND  (Saving4)>(Saving7) AND  (Saving4)>(Saving8) AND  (Saving4)>(Saving9) AND  (Saving4)>(Saving10) Then
    Value = (Saving4)
Else If (Saving5)>(Saving6) AND  (Saving5)>(Saving7) AND  (Saving5)>(Saving8) AND  (Saving5)>(Saving9) AND  (Saving5)>(Saving10) Then
    Value = (Saving5)
Else If (Saving6)>(Saving7) AND  (Saving6)>(Saving8) AND  (Saving6)>(Saving9) AND  (Saving6)>(Saving10) Then
    Value = (Saving6)
Else If (Saving7)>(Saving8) AND  (Saving7)>(Saving9) AND  (Saving7)>(Saving10) Then
    Value = (Saving7)
Else If (Saving8)>(Saving9) AND  (Saving8)>(Saving10) Then
    Value = (Saving8)
Else If (Saving9)>(Saving10) Then
    Value = (Saving9)
Else
    Value = (Saving10)
End If
End If
End If
End If
End If
End If
End If
End If
End If

 

This feels very clunky to me though as I am sure there is a better way of doing it!

It also doesn't seem to be working correctly as it always gives the result as the saving which has the highest value first digit. Eg. 99 would be the result even against values like 1000

All the savings values are calculated from 'Selling' and 'RRP' fields 1->10.

 

Any pointers would be much appreciated!

4 commentaires

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

I'm a VB rookie myself, I looked through the wizard functions in the VB builder and didn't see anything like Max() that would do what you are looking for.  What I can say is that by using the If and Else if statements I can see why you are getting results that are wrong.  Essentially what is happening is that after it finds the first value that is greater it jumps out of your Else if loop, this is programically how the function works.

 

Someone may have a better idea but I would create a variable to hold a value and compare each value with the next value and store it in the variable if it is larger using If Than funcitons.  These will run only if the if statement is true and will not have the same issue as the else if.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

I have worked out why it wasn't showing the correct value as one of the substring inputs had an error in it which meant it could have been a string.

I've converted all the substrings using CDbl after correcting the error and the result is now correct, just clunky!

No longer any urgency to this but it would be nice to get an VB expert opinion on improving the script.

0
Avatar
Ian Einman
Actions pour les commentaires Permalien

In "functions and subs" declare a Max function like this:

 

function Max(a,b) 
    Max = a 
    If b > a then Max = b 
end function 

Then use it like this:

 

Value = Max(Saving1, Max(Saving2, Max(Saving3, Saving4)))

This takes the max of 4 values but you should be able to see the pattern to extend it to as many values as you want.

 

This will be much easier to maintain.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

Great, thanks for the solution.

Vous devez vous connecter pour laisser un commentaire.