Slow Looping
Saw the reply, and tested it - you could try this (which
is much quicker - probably because it is not setting the
boolean to false for each row - but might not be better).
Sub Test2()
Dim iLast_Row As Integer
Dim j As Long
iLast_Row = 32000
For j = 1 To iLast_Row
Application.StatusBar = "Row " & j
If Worksheets("Sheet1").Cells(j, 1) = Worksheets
("Sheet2").Cells(j, 1) And Worksheets("Sheet1").Cells(j,
2) = Worksheets("Sheet2").Cells(j, 2) Then
GoTo skip_it
Else
Worksheets("Sheet2").Cells(j, 3).Value = "XXXXX"
End If
skip_it:
Next
End Sub
-----Original Message-----
Matt,
I can think of quicker ways. Merging, Sorting, etc.. But
here's the quick
and ugly. Not sure how it'll perform over 32000 rows -
probably poorly.
Assumes data is in columns A and B and that XXXXX is
written to column C
Sub testit()
Dim wksS As Worksheet, wksD As Worksheet
Dim i As Long, j As Long, lngLastRowS As Long,
lngLastRowD As Long
Dim blnFound As Boolean
Set wksS = Sheet2
Set wksD = Sheet1
lngLastRowS = wksS.Cells(Rows.Count, 1).End(xlUp).Row
lngLastRowD = wksD.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lngLastRowS
blnFound = False
For j = 1 To lngLastRowD
If wksS.Cells(i, 1) = wksD.Cells(j, 1) And
wksS.Cells(i, 2) =
wksD.Cells(j, 2) Then
blnFound = True
Exit For
End If
Next
If Not blnFound Then wksS.Cells(i, 3) = "XXXXX"
Next
End Sub
Rob
"Matt" wrote in message
...
I'm trying to over come a problem of slow looping, I
have 2 sheets both
of
which have about 32,000 rows when I use a macro with
loops it takes for
ever
to find the entries that are on one sheet but not on
another. I have
tried
the formala below and have tried to adapt it to suit my
needs but can not
get it to work. My two sheets have two columns and
both of them havve to
match. In the example below I need to isolate the row
with the xxxxxx
beside it. I has invoice numbers that are on the other
sheet and the same
amount but the combination of invoice number and amount
is unique. All the
other pairs have a match on the other sheet. Any help
would be greatly
appreciatied.
=IF(COUNTIF($A$1:$A$10,B1)=0,B1,"")
sheet1 sheet 2
inv number amount invoice no amount
104546 $ 250.25 154265 $ 4,625.00
451567 $ 1,000.25 254256 $ 1,456.45
254256 $ 1,456.45 451567 $ 25.60
451453 $ 65,452.00 XXXXXXX 154265 $
25.60 XXXXX
654245 $ 450.96 451453 $ 65,452.00
324154 $ 6,542.25 654245 $ 450.96
154265 $ 4,625.00 104546 $ 250.25
451567 $ 25.60 324154 $ 6,542.25
654245 $ 1,456.20 654245 $ 1,456.20
254256 $ 250.25 254256 $ 250.25
324154 $ 1,000.25 451567 $ 1,000.25
451567 $ 25.60 324154 $ 1,000.25
451567 $ 25.60
Matt
.
|