Version Checking on VBA (excel) code (redhat)

Question

enter image description here

Here my example of thing that i will use.

On the left side is the patch it will use NAME BASE REVISE to check the version of package.

Can you convert the script here in to VBA code. I will study about it and integrate to my real work:

if (Patch name = Pack name) then   **** searching for same Name on patch column to reference for patch base and revise number    
       if (base(c column) > base(h column)) ***checknumber[cellbycell] 

           display "yes" in J cell

             or if (base(C column) = base(h column)) then

                    check if revise(D column) > revise(I column)

                      display "yes" in J cell
    else display No

So if you can give me example code ; if you have sometime please explain to me that what each line of code is meaning.

This would get you started msdn.microsoft.com/en-us/library/office/… — Siddharth Rout
– Siddharth Rout, Commented Jan 11, 2013 at 7:20

Nick Perkins · Accepted Answer · 2013-01-11 07:22:42Z

1

You don't need vba for this

=IF($A2=$G2,IF($C2>$H2,"Yes",IF($C2=$H2,IF($D2>$I2,"Yes","No"),"No")),"No")

That goes in column J

answered Jan 11, 2013 at 7:22

Nick Perkins

1,3252 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Perkins Over a year ago

Of course I realise that this won't work with the dashes in there at the moment. You would need to do some custom coding (in VBA or formula) to tell excel how to determine how string A is greater than string B. Perhaps split columns on the dashes if you can. Seeing if Column A = Column G isn't an issue, it's only for columns C and H I can see being an issue.

eathapeking Over a year ago

yes becuase it's like x.x.x - x.x.x and some time we have to compare with unequal point of number like 2.1.3.1 and 2.13.1 ; excel funtion is not capable of doing this (i think it actually can but then i have to split every single char to each cell to compare it. waste of space) my bad that i'm not trying to learn VBA before T^T

whytheq · Accepted Answer · 2013-01-11 07:49:20Z

1

something like this should work:

Option Explicit

Sub variousconditions()

Dim i As Integer, x As Integer
x = 0
For i = 2 To 10
    With Excel.ThisWorkbook.ActiveSheet
        If .Cells(i, 1) = .Cells(i, 7) Then '****searching for same Name on patch
            Select Case .Cells(i, 3) '***checknumber[cellbycell]
                    Case Is > .Cells(i, 8)
                            .Cells(i, 10) = "yes"
                    Case Is = .Cells(i, 8)
                            If .Cells(i, 4) > .Cells(i, 9) Then
                                .Cells(i, 10) = "yes"
                            End If
                End Select
        End If
    End With
Next i

End Sub

I have to re-iterate Siddharth's reference as that will tell you where you need to save this code etc. : http://msdn.microsoft.com/en-us/library/office/ee814737%28v=office.14%29.aspx

answered Jan 11, 2013 at 7:49

whytheq

35.7k69 gold badges179 silver badges283 bronze badges

1 Comment

eathapeking Over a year ago

thank you but it still dont work well it only show the first CCC- but not the other CCC- it only show 3 result of yes

James Snell · Accepted Answer · 2013-01-11 16:29:25Z

1

Here is a function to compare two dot-notation version numbers which you'd need to paste into a new module in the VBA editor.

Option Explicit

Public Function VersionCompare(CurrentVersion As Range, _
                               TargetVersion As Range)

    Dim result As Integer
    result = CompareDotStrings(CurrentVersion.Cells(1, 1).Value, _
                               TargetVersion.Cells(1, 1).Value)

    If result = 1 Then
        VersionCompare = True
    Else
        VersionCompare = False
    End If
End Function

Private Function CompareDotStrings(LeftValue As String, _
                                  RightValue As String) _
                                  As Integer

    Dim CompareLeft() As String, CompareRight() As String, CompareLength As Integer

    CompareLeft = Split(LeftValue, ".")
    CompareRight = Split(RightValue, ".")
    CompareLength = UBound(CompareLeft)
    If UBound(CompareRight) < CompareLength Then CompareLength = UBound(CompareRight)

    Dim ElementLeft As Integer, ElementRight As Integer, Comparison As Integer
    Dim ElementNumber As Integer

    For ElementNumber = 0 To CompareLength
        ElementLeft = CInt(CompareLeft(ElementNumber))
        ElementRight = CInt(CompareRight(ElementNumber))
        Comparison = ElementRight - ElementLeft
        If Comparison <> 0 Then
            CompareDotStrings = Sgn(Comparison)
            Exit Function
        End If
    Next ElementNumber
    CompareDotStrings = 0
End Function

With this you can use =VersionCompare(H2, C2) to compare two version numbers and everything else you want to do (like splitting apart the dashed versions) can be done with formulas in the worksheet.

answered Jan 11, 2013 at 16:29

James Snell

6024 silver badges13 bronze badges

2 Comments

eathapeking Over a year ago

Jame ! what if it compare between 3 dot and 2 dot. is it okay?........ Like 3.3.10 compare to 3.3.1.2 or do i have to add any line of code ......... Thank You so much for your answer

James Snell Over a year ago

You're welcome - It will compare what is available to compare. So in the example it will compare 3.3.10 with 3.3.1 as there is nothing to compare the 2 with. You could add code to compare UBounds in the event of a so that in the event of a tie the version with more detail wins.

Collectives™ on Stack Overflow

Version Checking on VBA (excel) code (redhat)

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related