Matching values in different columns/workbooks

[MJKelly]

Hi, The code below loops through each cell in one column of data and
finds the same value in another column (on another spreadsheet). This
works fine for a 100 cells, but the final version will check 600 rows
in seven worksheets, and this takes an age. How can I do this more
efficiently? Would it be better to use an array (not sure how to do
this), and I assume that when the correct value is found, the
remaining cells are still searched? Could I use a function which finds
the match and so skips checking the rest of the column?


For Each r In Workbooks("02 - Scheduler
Plan.xls").Worksheets(SchedulerDayName).Range("B2:B100").Cells
If IsNumeric(r.Value) Then
For Each c In Workbooks("00 -
MDS.xls").Worksheets(MDSDayName).Range("C4:C1000").Cells
If c.Value = r.Value Then
c.offset(0, 2).Resize(1, 145).Copy
r.offset(0, 4).PasteSpecial xlPasteValues
End If
Next c
End If
Next r

hope you can help.

regards,
Matt

 

[Gary Keramidas]

make sure you turn off calculation and screen updating before running your code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' your code

Application.ScreenUpdating =True
Application.Calculation = xlCalculationAutomatic

 

[MJKelly]

Thanks Gary.
I know this would speed up the routine, but is the approach I've used
sound? I thought an array would make this faster and I'm sure there
must be a way of moving to the next step instead of checking the
remainder of a column once the match has been found?

Matt

 

[Ardus Petus]

Use UsedRAnge instead of Cells.
Cells returnrs ALL cells in your worksheet
UsedRange returns only used cells


For Each r In Workbooks("02 - Scheduler
Plan.xls").Worksheets(SchedulerDayName).Range("B2:B100").UsedRange
If IsNumeric(r.Value) Then
For Each c In Workbooks("00 -
MDS.xls").Worksheets(MDSDayName).Range("C4:C1000").UsedRange
If c.Value = r.Value Then
c.offset(0, 2).Resize(1, 145).Copy
r.offset(0, 4).PasteSpecial xlPasteValues
End If
Next c
End If
Next r

HTH

 

[Per Jessen]

Hi Matt

I don't think you can improve the routine to speed it up except from adding
"Exit For" after this linge:

r.Offset(0, 4).PasteSpecial xlPasteValues

Hopes it helps.

Regards,
Per

 

[MJKelly]

Thanks for this Gents, the code is running faster now (66% faster!).

Ardus,
When I tried using your suggestion I got a run-time error (438)?

Per,
Is the "exit for" skipping the remainder of the column once a match
has been found? If so this is what I was trying to do. Thanks very
much.

Kind regards,
Matt

 

[Per Jessen]

Hi Matt

Thanks for your reply.

The "exit for" is skipping the inner for/next loop once a match has been
found in the column, and continues with next value in column B.

Best regards
Per

 

[nikolai]

This

r.Offset(0, 4).Resize(1, 145) = c.Offset(0, 2).Resize(1, 145).Value

instead of

            c.offset(0, 2).Resize(1, 145).Copy
            r.offset(0, 4).PasteSpecial xlPasteValues

should work much faster.

My solution is

For Each r In Workbooks("02 -
SchedulerPlan.xls").Worksheets(SchedulerDayName).Range("B2:B100")
If IsNumeric(r.Value) Then
countNames = WorksheetFunction.CountIf(Workbooks("00 -
MDS.xls").Worksheets(MDSDayName).Range("C4:C1000"), r.Value)
If countNames > 0 Then
Set c =
Workbooks("MDS.xls").Worksheets(1).Range("C4:C1000").Find(r.Value,
LookAt:=xlWhole)
r.Offset(0, 4).Resize(1, 145) = c.Offset(0, 2).Resize(1,
145).Value
End If
End If
Next

 

[MJKelly]

Per,
Thanks, I thought that's what it must be doing.

nikoiao,
Thanks very much. Yes it does run much faster again. I guess because
I am not copying pasting the speed is increased?

Thanks everyone for your help. The code now runs in less than 4
seconds where as before it never really finished, it was just hanging,
and even applying these suggestions one at a time, the code has run
around 60% faster.

I have been told that copying/pasting takes up memory and excel is
pretty rubbish at clearing this memory, so over time as the
application is open and used it will start to run slower. Is this
your experience/understanding? This is why I was interested in using
an array as I am told the memory can be cleared?

Thanks,
Matt