# 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 Sub

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

• Edited by 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 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.
'---
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 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