PDA

View Full Version : [SOLVED] VBA - Random Comparison Columns



kiirito
12-03-2014, 07:52 AM
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.
Thanks

iMadejoolaff
12-04-2014, 03:06 PM
I am by no means a VBA guru, so just laugh if I'm wrong.

Shouldn't you dimension RndNum as Long in the header section?

kiirito
12-05-2014, 02:16 AM
Its not needed since its just a random int number.

mancubus
12-05-2014, 03:18 AM
why do you want to compare each cell in column E to one random cell in column F?

assume i = 2 and RndNum is 135 in the first loop.
you are testing E2 against F135.
what if F68 is equal to E2?



PS: care to post the comments/requirements out of quote/code/php tags in your messages please.

kiirito
12-05-2014, 03:21 AM
Thats why I cant get it work .
The thing is that I dont know in which row of the sheet2 is the value im looking for from the sheet1.

mancubus
12-05-2014, 03:29 AM
so be specific. clearly describe your requirement.

like so:
i want to check if all values in colum E exists in column F.
if exists, do this/nothing; if not, do that/nothing.

columns can be in different sheets.

kiirito
12-05-2014, 03:35 AM
Thanks for the advice, that was in my thoughts the easier way to explain it. Nevermind .

Anyway, do you know actually how to solve that problem ?

mancubus
12-05-2014, 03:46 AM
i still dont know what you requirment is.
i can just quess...

one way to test if a cell value exists in another range, column F in sh2 for your case:



If Application.CountIf(sh2.Columns(6), sh1.Cells(i, 5)) > 0 Then
'your code when condition is met
Else
'your code when condition is not met
End If

kiirito
12-05-2014, 03:54 AM
I can't edit the first post so i'll just rewrite it here with an easier way to get the requirment.
Basically:
i want to check if all values in column E exists in column F.
Those columns are in different workbooks ( there is no problem about it since I know how to compare them even if they are in different workbooks )
The sheets have different ranges. ( basically sheet1 can have more rows than the sheet2 and viceversa ).

If the first check is correct then write in the cell by me indicated : Found AND compare the values in € that are in other cells ( this part is actually the easier part , lets just get on with the first compare, then i might get this one in my own ).

If its not correct then just write in the cell by me indicated : Not found.

NOTE THAT : The values that are in column E and column F are the company names. So its a string.

There you go

mancubus
12-05-2014, 05:15 AM
those lines are posted serve that requirement.

from the code you posted i assumed you are familiar with VBA programming. you just need to adopt them to nested Ifs in your code.

kiirito
12-05-2014, 05:36 AM
Actually im not that familiar with VBA . The code I have its wrote from different helps by my friends.
Im more in C# / C++ .
Anyway, im gonna try those lines

kiirito
12-05-2014, 05:51 AM
Thats the new code :

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
If Application.CountIf(sh2.Columns(6), sh1.Cells(i, 5)) > 0 Then
If Application.CountIf(sh2.Columns(9), sh1.Cells(i, 6)) Then
sh1.Cells(i, 7) = "Trovato"
If sh1.Cells(i, 10).Value = sh2.Cells(i, 10).Value Then
sh1.Cells(i, 8) = "OK"
Else
sh1.Cells(i, 8) = "Valore Errato"
If sh1.Cells(i, 11).Value = sh2.Cells(i, 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
End If
Next
End Sub


But its not working that good, well actually I think there is a problem with the For but I cant get it.

mancubus
12-05-2014, 06:50 AM
you have to organize the conditions so well that the nested if functions will produce the desired result.

i adopted the code in the first mesaage.

condition: sh1.E exists in sh2.F AND sh1.F exists in sh2.I
****true
********1) write "Trovato" in sh1.G
********2) condition: sh1.J exists in sh2.J
************true
****************write "OK" in sh1.H
************false
****************write "Valore Errato" in sh1.H
********3) condition: sh1.K exists in sh2.K
************true
****************write "OK" in sh1.I
************false
****************write "Valore Errato" in sh1.I
****false
********1) write "Non Trovato" in sh1.G

if you feel i dont get your requirement correctly, describe it in a similar logic.



Sub Compare_Two_Workbooks()
'http://www.vbaexpress.com/forum/showthread.php?51286-VBA-Random-Comparison-Columns
'Declarations
Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet

'Variable Assigments
Set wb1 = Workbooks.Open("C:\A.xlsx")
Set wb2 = Workbooks.Open("C:\B.xlsx")
Set sh1 = wb1.Sheets("Foglio1")
Set sh2 = wb2.Sheets("Foglio1")

For i = 2 To sh1.Cells.Find("*", , , , xlByRows, xlPrevious).Row
'sh1.E exists in sh2.F AND sh1.F exists in sh2.I
If Application.CountIf(sh2.Columns(6), sh1.Cells(i, 5)) > 0 And _
Application.CountIf(sh2.Columns(9), sh1.Cells(i, 6)) > 0 Then
'Two conditions are met. Do the following 3:
'1) write "Trovato" in sh1.G
sh1.Cells(i, 7) = "Trovato"
'2) condition: sh1.J exists in sh2.J
If Application.CountIf(sh2.Columns(10), sh1.Cells(i, 10)) > 0 Then
'write "OK" in sh1.H
sh1.Cells(i, 8) = "OK"
Else
'write "Valore Errato" in sh1.H
sh1.Cells(i, 8) = "Valore Errato"
End If
'3) condition: sh1.K exists in sh2.K
If Application.CountIf(sh2.Columns(11), sh1.Cells(i, 11)) > 0 Then
'write "OK" in sh1.I
sh1.Cells(i, 9) = "OK"
Else
'write "Valore Errato" in sh1.I
sh1.Cells(i, 9) = "Valore Errato"
End If
Else
'Two conditions are not met. Do the following:
sh1.Cells(i, 7) = "Non trovato"
End If
Next
End Sub

kiirito
12-05-2014, 06:56 AM
Nevermind, the code works sir. thank you very much really appreciate your help

mancubus
12-05-2014, 07:15 AM
you are welcome.
please mark the thread as SOLVED from the "thread tools" dropdown which is above the first post.

snb
12-05-2014, 09:26 AM
Or


Sub M_snb()
sn = Workbooks("A.xlsx").Sheets("Foglio1").Cells(1).CurrentRegion
With Workbooks("B.xlsx").Sheets("Foglio1")
sp = .Cells(1).CurrentRegion.Columns(6)
sq = .Cells(1).CurrentRegion.Columns(9)
sr = .Cells(1).CurrentRegion.Columns(10)
st = .Cells(1).CurrentRegion.Columns(11)
End With

For j = 1 To UBound(sn)
sn(j, 7) = IIf((Not IsError(Application.Match(sn(j, 5), sp, 0))) * (Not IsError(Application.Match(sn(j, 6), sq, 0))), "", "non ") & "Trovato"
sn(j, 8) = IIf(Not IsError(Application.Match(sn(j, 10), sr, 0)), "OK", "Valore Errato")
sn(j, 9) = IIf(Not IsError(Application.Match(sn(j, 11), st, 0)), "OK", "Valore Errato")
Next

Workbooks("A.xlsx").Sheets("Foglio1").Cells(1).CurrentRegion = sn
End Sub