Asked by:
Random Comparison Columns
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 SubI don't know how to modify it and make a RANDOM comparison.
 Edited by Kiirito Thursday, December 4, 2014 9:38 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 addins & workbooks)
 Edited by James Cone Sunday, October 30, 2016 11:42 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

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")