comparing two excel sheets Can someone see whats wrong with my code? 1

[dorjee]

Hi
I want to compare two excel sheets. I have to find each row in the first one that is not in the second one and extract it to a third sheet. The comparison is done just on one cell of the rows, some kinfd of id.
I have the following code, but i get an error that is not defined by VBA. I don t know what s wrong. Can someone see the light?
thanks

 

[dorjee]

this is the code:
Sub CompareSheets()
Dim lRow As Long, cols As Integer, i As Integer, u As Integer, refArr As Variant, CompArr As Variant
Dim refSht As Worksheet, compSht As Worksheet, incr As Long, test As Boolean

Set refSht = Sheets("Sheet1&quot
Set compSht = Sheets("Sheet2&quot
Application.ScreenUpdating = False

lRow = refSht.UsedRange.Rows.Count
cols = refSht.UsedRange.Columns.Count
incr = 1
For i = 1 To lRow
refSht.Select
refArr = refSht.Cells(i, 3)

compSht.Select

test = True
For u = 1 To lRow
CompArr = compSht.Cells(u, 3)
If refArr = CompArr Then
test = False
u = lRow
End If
Next
If test = True Then
With Sheets("Sheet3&quot
.Range(incr, 1).Value = refSht.UsedRange.Cells(i, 1)
.Range(incr, 2).Value = refSht.UsedRange.Cells(i, 2)
.Range(incr, 3).Value = refSht.UsedRange.Cells(i, 3)
.Range(incr, 4).Value = refSht.UsedRange.Cells(i, 4)
.Range(incr, 5).Value = refSht.UsedRange.Cells(i, 5)
.Range(incr, 6).Value = refSht.UsedRange.Cells(i, 6)
.Range(incr, 7).Value = refSht.UsedRange.Cells(i, 7)
incr = incr + 1
End With
End If

Next i
End Sub

 

[PHV]

What is the exact error message and on which line ?

 

[MakeItSo]

Yepp. Several things, all to do with the same variables:

Dim ... refArr As Variant, CompArr As Variant

You have not defined nor initialized them as arrays.

Do so with either
Dim refArr() As Variant, CompArr() As Variant

or with

refArr=Array(2) 'starting index 0, so it's array fields 0 and 1
CompArr=Array(2)

Then you need to a) index the arrays when assigning values and b) make sure you do not overflow their indexes:
As Arrays start with index 0:
[blue]
refArr(0)=""
compArr(0)=""
For i = 1 To lRow
Redim Preserve refArr(i+1)
refSht.Select
refArr(i) = refSht.Cells(i, 3)

compSht.Select

test = True
For u = 1 To lRow
Redim Preserve CompArr(u+1)
CompArr(u) = compSht.Cells(u, 3)
If refArr(i) = CompArr(u) Then
[/blue]

Not sure if I caught it all, but it should give you an idea of what is wrong. ;-)

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[xlbo]

Thought that looked familiar - obviously you didn't quite get what the code was doing - this is the code I posted originally:

Sub GetDiffs()
Dim lRow As Long, cols As Integer, i As Integer, refArr As Variant, CompArr As Variant
Dim refSht As Worksheet, compSht As Worksheet, incr As Long
Set refSht = Sheets("Sheet1&quot
Set compSht = Sheets("Sheet2&quot
Application.ScreenUpdating = False

lRow = refSht.UsedRange.Rows.Count
cols = refSht.UsedRange.Columns.Count
incr = 1
For i = 1 To cols
refSht.Select
refArr = refSht.Range(Cells(1, i), Cells(lRow, i))
compSht.Select
CompArr = compSht.Range(Cells(1, i), Cells(lRow, i))
For x = 1 To UBound(refArr)
If refArr(x, 1) <> CompArr(x, 1) Then
With Sheets(&quot;Sheet3&quot
.Cells(incr, 1).Value = &quot;R&quot; & x + 1 & &quot;C&quot; & i
.Cells(incr, 2).Value = refArr(x, 1)
.Cells(incr, 3).Value = CompArr(x, 1)
incr = incr + 1
End With
Else
End If
Next

Next i

End Sub


This code assigns a RANGE of cells to an array - you are trying to assign one cell to an array. This loads a column of data (up to the last row) into each array and then compares the 2 arrays - it does this for each column in the used range - 'm not entirely sure why you have changed it as you have ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[dorjee]

ok,
xlbo, i have indeed taken your code that i tried to customize. As you could see, I am not very familiar with the datatype and methods in vba..
What I tried to do i compare the value in a cell of column 3 in sheet1 with all the values in the same column in sheet 2. If this value is not found in column 3 of sheet 2, then extract the whole row from sheet 1 and copy in sheet 3. The code should then proceed the same way with all values in column 3 of sheet 1.
Thank you all for your help.

 

[xlbo]

So you are not doing a 1 to 1 match then - you are checking to see if a value in a reference sheet appears at all in another sheet ??

In that case, you would be better off using the FIND method....

sub getdiffs2()
dim lrow as long, fCell as range, lCol as integer
Dim refSht As Worksheet, compSht As Worksheet, resSht as worksheet, incr As Long
Set refSht = Sheets("Sheet1")
Set compSht = Sheets("Sheet2")
set resSht = sheets("Sheet3")
Application.ScreenUpdating = False
'Get last row of column to check
incr = 1
lRow = refSht.cells(65536,[b]3[/b]).end(xlup).row
lCol = refSht.usedrange.columns.count
for each c in range("C1:C" & lRow)
  set fCell = compSht.columns("C").find(c.value,lookin:=xlvalues, lookat:=xlwhole)
    if fCell is nothing then
       'value not found
         refsht.range(cells(c.row,1),cells(c.row,lCol)).copy destination:= resSht.range("A" & incr)
         incr = incr + 1
    end if
next
end sub

should be nearer to what yuo need

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[dorjee]

hi xlbo, thanks for quick reponses (and responses at all)
There is one thing i am not sure i get from your code:
&quot;lRow = refSht.cells(65536,3).end(xlup).row&quot;
Wouldn t it be possible to initialize lrow the same way as lcol(lRow = refSht.UsedRange.Rows.count)? I have more than 65500 rows in the sheet..

 

[dorjee]

Another thing: c asks to be defined...

 

[xlbo]

yes - you can use lRow = usedrange.rows.count

I just use the other way through force of habit

all it does is perform the same action as starting in cell C65536, press the end key, then the up key - this way is probably quicker as the code doesn't have to count 65000 rows - just needs to do a move upwards from the bottom of the worksheet to the last row with data. BTW - if you are doing a search on 65500 + entries, this is gonna take quite some time

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

missed the note about c - you must have option explicit on

dim c as range

should sort that out

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[dorjee]

hi
I got an 1004 run time error with this exact code..
Have a clue?

 

[dorjee]

If it takes ages to do the search on +50000 entries, do you have an idea of a faster way to do the extraction?

 

[xlbo]

If you get a runtime error, how do you know how long it takes ?? have you sorted the error ?? it was probably to do with sheet names.

I did mention in a previous post that it'd take a while - it's gotta do 50000+ loops - it ain't gonna be quick, whatever. The only way I know of doing faster matching is to do an array compare (which is what my original code does) - but that relies on matching row to row - I guess you could load both sets of values into arrays but I doubt it'd speed things up much:

Option Base 1
Sub GetDiffs()
Dim lRow As Long, cols As Integer, i As Integer, refArr As Variant, CompArr As Variant
Dim refSht As Worksheet, compSht As Worksheet, incr As Long, found As Boolean
Set refSht = Sheets(&quot;Sheet1&quot
Set compSht = Sheets(&quot;Sheet2&quot
Application.ScreenUpdating = False

lRow = refSht.UsedRange.Rows.Count
lCol = refSht.UsedRange.Columns.Count
incr = 1

refSht.Select
refArr = refSht.Range(Cells(1, 3), Cells(lRow, 3))
compSht.Select
CompArr = compSht.Range(Cells(1, 3), Cells(lRow, 3))
For x = LBound(refArr) To UBound(refArr)
found = False
For y = LBound(CompArr) To UBound(CompArr)
If refArr(x, 1) = CompArr(y, 1) Then
found = True
Exit For

End If
Next y
If found = False Then
refSht.Range(Range(Cells(x, 1), Cells(x, lCol)).Address).Copy Destination:=Sheets(&quot;Sheet3&quot.Range(&quot;A&quot; & incr)
incr = incr + 1
End If
Next x
End Sub



Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[dorjee]

thanks man! it works

 

[xlbo]

Is it quicker ??
I havn't had chance to test it on any significant amount of data
I would've thought it'd take longer as for each cell in the reference sheet, it has to loop throught the array of data in the comparison sheet until it finds a match (or not)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[dorjee]

yeah well I dont know if its faster than the solution you suggested with the arrays. Could you possibly give me a hink of the code to add with arrays?

 

[dorjee]

xlbo, when you wrote that the array solution would rely on matching row to row, do tou mean that each row is compared only to the row in compsheet with the same row number?

 

[xlbo]

eh ??
the last bit of code I posted uses arrays

the 1st code I posted was an array to array match where

refArr(1,1) is compared to compArr(1,1) and no other elements (that's the code you tried to adjust originally)

the 2nd bit of code I posted used the FIND method - you said it took too long so I posted another version of an array match where each element of the refArr is compared to ALL elements of compArr

Was just asking which worked (both shouldwork) and which was faster

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[dorjee]

well, the last solution (not Find) seems to take shorter time.
But I tested on 10000 rows only. when i tested on 20000, the process would end up not responding and I had to abort it. I can't use a row match solution as they are changing place from a file to another. So I am trying to figure out how to speed up the exctraction...
Any ideas welcomed!
thanks