Could someone tell me how to compare one worksheet with another. I'm using
looping but this is just too slow. Could someone tell me how to compare one
sheet with another and drop matching reference numbers beside each matching
number as in the below code. I'm thinking maybe using the Find method would
be faster but am not sure how to use it even after looking at the help..
There is 12,000 rows on the first sheet and 35,000 on the second. Any help
would be much appreciated.


Sub MatchUp()

Dim wss As Object
Dim wsc As Object
Dim T, I
Set wss = Worksheets("Supplers")
Set wsc = Worksheets("11360")

I = 6
Do While wsc.Cells(I, 1) < "END"
T = 6
Do While wss.Cells(T, 1) < "END"
If -wsc.Cells(I, 7) = wss.Cells(T, 5) And wsc.Cells(I, 3) = wss.Cells(T, 3)
And wsc.Cells(I, 8) = "" Then
wsc.Cells(I, 8) = I
wss.Cells(T, 6) = I
End If

T = T + 1
Loop
I = I + 1
Loop
End Sub

Matt,

12,000 Finds will take time as well.

Couple of things you could do is

- turn off screen updating

Application.ScreenUpdating = False

- turn off automatic calculation

Application.Calculation = xlCalculationManual

Be sure to reset at some point though (True and xlCalculationAutomatic).

If this isn't enough, another way is to use a filter and create a test
column that can be filtered, and then delete rows that match. Post back if
you want to follow this option.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Matt" wrote in message
...
Could someone tell me how to compare one worksheet with another. I'm
using
looping but this is just too slow. Could someone tell me how to compare
one
sheet with another and drop matching reference numbers beside each
matching
number as in the below code. I'm thinking maybe using the Find method
would
be faster but am not sure how to use it even after looking at the help..
There is 12,000 rows on the first sheet and 35,000 on the second. Any
help
would be much appreciated.


Sub MatchUp()

Dim wss As Object
Dim wsc As Object
Dim T, I
Set wss = Worksheets("Supplers")
Set wsc = Worksheets("11360")

I = 6
Do While wsc.Cells(I, 1) < "END"
T = 6
Do While wss.Cells(T, 1) < "END"
If -wsc.Cells(I, 7) = wss.Cells(T, 5) And wsc.Cells(I, 3) = wss.Cells(T,
3)
And wsc.Cells(I, 8) = "" Then
wsc.Cells(I, 8) = I
wss.Cells(T, 6) = I
End If

T = T + 1
Loop
I = I + 1
Loop
End Sub

See Chip Pearson's page for a variety of methods:

http://www.cpearson.com/excel/duplicat.htm

--
regards,
Tom Ogilvy


Matt wrote in message
...
Could someone tell me how to compare one worksheet with another. I'm
using
looping but this is just too slow. Could someone tell me how to compare
one
sheet with another and drop matching reference numbers beside each
matching
number as in the below code. I'm thinking maybe using the Find method
would
be faster but am not sure how to use it even after looking at the help..
There is 12,000 rows on the first sheet and 35,000 on the second. Any
help
would be much appreciated.


Sub MatchUp()

Dim wss As Object
Dim wsc As Object
Dim T, I
Set wss = Worksheets("Supplers")
Set wsc = Worksheets("11360")

I = 6
Do While wsc.Cells(I, 1) < "END"
T = 6
Do While wss.Cells(T, 1) < "END"
If -wsc.Cells(I, 7) = wss.Cells(T, 5) And wsc.Cells(I, 3) = wss.Cells(T,
3)
And wsc.Cells(I, 8) = "" Then
wsc.Cells(I, 8) = I
wss.Cells(T, 6) = I
End If

T = T + 1
Loop
I = I + 1
Loop
End Sub

"Tom Ogilvy" schreef in bericht
...
See Chip Pearson's page for a variety of methods:

http://www.cpearson.com/excel/duplicat.htm


see:
http://users.skynet.be/onderland/Excel.htm
dubbelgangers.

a .xla add-in for difference between sheets on rows or on records.
try it

--
regards,
Tom Ogilvy


Matt wrote in message
...
Could someone tell me how to compare one worksheet with another. I'm
using
looping but this is just too slow. Could someone tell me how to compare
one
sheet with another and drop matching reference numbers beside each
matching
number as in the below code. I'm thinking maybe using the Find method
would
be faster but am not sure how to use it even after looking at the help..
There is 12,000 rows on the first sheet and 35,000 on the second. Any
help
would be much appreciated.


Sub MatchUp()

Dim wss As Object
Dim wsc As Object
Dim T, I
Set wss = Worksheets("Supplers")
Set wsc = Worksheets("11360")

I = 6
Do While wsc.Cells(I, 1) < "END"
T = 6
Do While wss.Cells(T, 1) < "END"
If -wsc.Cells(I, 7) = wss.Cells(T, 5) And wsc.Cells(I, 3) = wss.Cells(T,
3)
And wsc.Cells(I, 8) = "" Then
wsc.Cells(I, 8) = I
wss.Cells(T, 6) = I
End If

T = T + 1
Loop
I = I + 1
Loop
End Sub

Thanks Bob

I would be interested in finding the solution you suggest using filtering.


"Bob Phillips" wrote in message
...
Matt,

12,000 Finds will take time as well.

Couple of things you could do is

- turn off screen updating

Application.ScreenUpdating = False

- turn off automatic calculation

Application.Calculation = xlCalculationManual

Be sure to reset at some point though (True and xlCalculationAutomatic).

If this isn't enough, another way is to use a filter and create a test
column that can be filtered, and then delete rows that match. Post back if
you want to follow this option.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Matt" wrote in message
...
Could someone tell me how to compare one worksheet with another. I'm
using
looping but this is just too slow. Could someone tell me how to compare
one
sheet with another and drop matching reference numbers beside each
matching
number as in the below code. I'm thinking maybe using the Find method
would
be faster but am not sure how to use it even after looking at the help..
There is 12,000 rows on the first sheet and 35,000 on the second. Any
help
would be much appreciated.


Sub MatchUp()

Dim wss As Object
Dim wsc As Object
Dim T, I
Set wss = Worksheets("Supplers")
Set wsc = Worksheets("11360")

I = 6
Do While wsc.Cells(I, 1) < "END"
T = 6
Do While wss.Cells(T, 1) < "END"
If -wsc.Cells(I, 7) = wss.Cells(T, 5) And wsc.Cells(I, 3) = wss.Cells(T,
3)
And wsc.Cells(I, 8) = "" Then
wsc.Cells(I, 8) = I
wss.Cells(T, 6) = I
End If

T = T + 1
Loop
I = I + 1
Loop
End Sub