I have two numbers, they are version numbers, I am having problems with my If then else and elseif statements.
59821 for version 5.9.8.2.1
is more than
5983 for version 5.9.8.3
even though 5.9.8.3 is a greater version number. Do you see where my problem is. How do I compare # by # reading it, checking to see what one is bigger and going from there.
so when it gets to the 3 of the 5983 its bigger than 2 of 5982 of the 5.9.8.2.1
Am I making any since?
Compare Version Number Strings
-
-
Re: If Then Else Elseif Comparing Text Numbers
You could use something like:
=A1*10^(4-INT(LOG(A1)))
To make them the same lenght and then compare.
TJ
-
Re: If Then Else Elseif Comparing Text Numbers
Thanks that worked when I put it on the sheet. I couldn't figure out how to make that into VBA to do the same. Thanks again!
-
Re: Compare Version Number Strings
I have the same problem, so I wrote a quick function to solve the problem. It works quite well and even if you use letters in your version it still works. Obly if you compare capital with small letters weird things are happening (but for my purpose there are just small letters in use).
Code
Display MoreFunction VersionCompare(Version1 As String, Version2 As String) As Integer'returns 1 if Version 1 is newer 'returns -1 if version 1 is older 'returns 0 if both versions are the same Dim i As Integer Dim Version1Array() As String Dim Version2Array() As String Version1Arry = Split(Version1, ".") Version2Arry = Split(Version2, ".") For i = 0 To Application.Min(UBound(Version1Arry), UBound(Version2Arry)) If Version1Arry(i) > Version2Arry(i) Then VersionCompare = 1 Exit For ElseIf Version1Arry(i) < Version2Arry(i) Then VersionCompare = -1 Exit For Else If UBound(Version1Arry) = UBound(Version2Arry) Then VersionCompare = 0 ElseIf UBound(Version1Arry) > UBound(Version2Arry) Then VersionCompare = 1 Else VersionCompare = -1 End If End If Next i End FunctionI am happy if you have some feedback or if you find a better solution/improvement. Thanks.
-
Re: Compare Version Number Strings
blablubbb Thank You. Just found a spelling error in Version1Array and changed the Application.Min function to standard vba:
Code
Display MorePublic Function VersionCompare(Version1 As String, Version2 As String) As Integer 'returns 1 if Version 1 is newer 'returns -1 if version 1 is older 'returns 0 if both versions are the same Dim i As Integer Dim Version1Array() As String Dim Version2Array() As String Version1Array = Split(Version1, ".") Version2Array = Split(Version2, ".") Dim k As Integer k = UBound(Version1Array) If UBound(Version2Array) < k Then k = UBound(Version2Array) For i = 0 To k If Version1Array(i) > Version2Array(i) Then VersionCompare = 1 Exit For ElseIf Version1Array(i) < Version2Array(i) Then VersionCompare = -1 Exit For Else If UBound(Version1Array) = UBound(Version2Array) Then VersionCompare = 0 ElseIf UBound(Version1Array) > UBound(Version2Array) Then VersionCompare = 1 Else VersionCompare = -1 End If End If Next i End Function -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!