none
Random Comparison Columns RRS feed

  • Question

  • I would like to compare two columns in a RANDOM way and not Row By Row . Thats the code I wrote for the Row by Row comparison

    Sub compare()

    Dim wb1 As Workbook: Set wb1 = Workbooks.Open("C:\A.xlsx")
    Dim wb2 As Workbook: Set wb2 = Workbooks.Open("C:\B.xlsx")

    Dim sh1 As Worksheet: Set sh1 = wb1.Sheets("Foglio1")
    Dim sh2 As Worksheet: Set sh2 = wb2.Sheets("Foglio1")

    Dim lr As Long, c As Range

    lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
        For i = 2 To lr
            RndNum = Int((200 - 2 + 1) * Rnd + 2) ' Random nr from range 2 to 200
            If sh1.Cells(i, 5).Value = sh2.Cells(RndNum, 6).Value And sh1.Cells(i, 6).Value = sh2.Cells(RndNum, 9).Value Then
                sh1.Cells(i, 7) = "Trovato"
                If sh1.Cells(i, 10).Value = sh2.Cells(RndNum, 10).Value Then
                    sh1.Cells(i, 8) = "OK"
                    Else
                        sh1.Cells(i, 8) = "Valore Errato"
                    If sh1.Cells(i, 11).Value = sh2.Cells(RndNum, 11).Value Then
                        sh1.Cells(i, 9) = "OK"
                    Else
                        sh1.Cells(i, 9) = "Valore Errato"
                    End If
                End If
            Else
                sh1.Cells(i, 7) = "Non trovato"
            End If
        Next
    End Sub

    I don't know how to modify it and make a RANDOM comparison.


    • Edited by Kiirito Thursday, December 4, 2014 9:38 AM
    Thursday, December 4, 2014 9:37 AM

All replies

  • Re:  random comparison

    How many of the 199 cells do you want to randomly compare?
    Are repeat random comparisons allowed?
    What version of Excel are you using?
    Note:  RndNum Variable is not declared.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 11:42 PM
    Thursday, December 4, 2014 1:59 PM
  • There are actually more than 199 cells, thats just a e.g. number.

    1) Yes

    2) Im using Excel 2010

    3) The code in the thread is not doing his job, probably its needed a Whole new code .

    Thanks.

    Thursday, December 4, 2014 2:57 PM
  • Re:  Compare code

    Here is a modified version of your code that I have not tested.
    It should give you a head start on your compare request...
    '---
    Sub compare()

    Dim wb1 As Workbook: Set wb1 = Workbooks.Open("C:\A.xlsx")
    Dim wb2 As Workbook: Set wb2 = Workbooks.Open("C:\B.xlsx")
    Dim sh1 As Worksheet: Set sh1 = wb1.Sheets("Foglio1")
    Dim sh2 As Worksheet: Set sh2 = wb2.Sheets("Foglio1")
    Dim rngOne As Range
    Dim rngTwo As Range
    Dim RndNum As Long
    Dim lngCount As Long
    Dim lngSampleTotal As Long
    Const dblPercent As Double = 0.11    '<<<<< adjust percentage

     Set rngOne = sh1.Range("B2:C200")   '<<<<< adjust range (same size as rngTwo)
     Set rngTwo = sh2.Range("D2:D200")  '<<<<< adjust range
     lngSampleTotal = rngOne.Cells.Count * dblPercent
     Randomize
     
     Do
       lngCount = lngCount + 1
      'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
       RndNum = Int(rngOne.Rows.Count * Rnd + 1)
       If rngOne.Cells(RndNum, 1).Value = rngTwo.Cells(RndNum, 1).Value And _
          rngOne.Cells(RndNum, 2).Value = rngTwo.Cells(RndNum, 2).Value Then
         rngOne.Cells(i, 7) = "Trovato"
         If rngOne.Cells(RndNum, 10).Value = rngTwo.Cells(RndNum, 10).Value Then
           rngOne.Cells(RndNum, 8) = "OK"
         Else
           rngOne.Cells(RndNum, 8) = "Valore Errato"
           If rngOne.Cells(RndNum, 11).Value = sh2.Cells(RndNum, 11).Value Then
              rngOne.Cells(RndNum, 9) = "OK"
           Else
              rngOne.Cells(RndNum, 9) = "Valore Errato"
           End If
         End If
       Else
         sh1.Cells(RndNum, 7) = "Non trovato"
       End If
     Loop Until lngCount > lngSampleTotal
     
    End Sub
    '---
    Jim Cone



    • Edited by James Cone Friday, December 5, 2014 7:10 PM
    Thursday, December 4, 2014 5:40 PM
  • Thanks for the code, I adjusted the ranges still I cant get it work with a right compare.

    Note that the range of the sheet1 is bigger than the range of the sheet2.

    Thats indeed the range I use :

      Set rngOne = sh1.Range("E2:E1424")   
      Set rngTwo = sh2.Range("F2:F962")

    Friday, December 5, 2014 8:37 AM