aadityapatel
07-03-2014, 12:34 AM
My worksheet 1 contains around 2000 rows in column1 and same no of rows contained in worksheet 2 in column1 Is there any way i can compare value(string or integer) in particular row in work sheet 1 to check it is present in any one the row in work sheet 2 I have written one sample vba code for this but its taking considerable amount to time to get the result
Sub Compare2WorkSheets(ws1 As Worksheet, ws2 As Worksheet)Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String, rowval1 As String, rowval2 As String
Dim report As Workbook, difference As Long
Dim exact As Long
Dim row As Long, col As Integer
With ws1.UsedRange
ws1row = .Rows.Count
ws1col = .Columns.Count
End With
With ws2.UsedRange
ws2row = .Rows.Count
ws2col = .Columns.Count
End With
difference = 0
exact = 0
For row = 1 To ws1row
rowval1 = ws1.Cells(row, 1).Formula
For row1 = 1 To ws1row
rowval2 = ws2.Cells(row1, 1).Formula
If rowval1 = rowval2 Then
For col = 1 To ws1col
colval1 = ""
colval2 = ""
colval1 = ws1.Cells(row, col).Formula
colval2 = ws2.Cells(row, col).Formula
If colval1 <> colval2 Then
difference = difference + 1
Else
exact = exact + 1
End If
Next col
End If
Next row1
Next row
Debug.Print difference
Debug.Print exact
'Debug.Print ws1row
'Debug.Print ws1col
End Sub
Sub Compare2WorkSheets(ws1 As Worksheet, ws2 As Worksheet)Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String, rowval1 As String, rowval2 As String
Dim report As Workbook, difference As Long
Dim exact As Long
Dim row As Long, col As Integer
With ws1.UsedRange
ws1row = .Rows.Count
ws1col = .Columns.Count
End With
With ws2.UsedRange
ws2row = .Rows.Count
ws2col = .Columns.Count
End With
difference = 0
exact = 0
For row = 1 To ws1row
rowval1 = ws1.Cells(row, 1).Formula
For row1 = 1 To ws1row
rowval2 = ws2.Cells(row1, 1).Formula
If rowval1 = rowval2 Then
For col = 1 To ws1col
colval1 = ""
colval2 = ""
colval1 = ws1.Cells(row, col).Formula
colval2 = ws2.Cells(row, col).Formula
If colval1 <> colval2 Then
difference = difference + 1
Else
exact = exact + 1
End If
Next col
End If
Next row1
Next row
Debug.Print difference
Debug.Print exact
'Debug.Print ws1row
'Debug.Print ws1col
End Sub