Home » Forums » Open Source Learning Center » Question and Answer

Help needed on Optimizing search operation

Submitted by megha_joshi on 15 June, 2010 - 04:37

Good Morning Everybody,

I am working with VBA and facing some problem as mentioned below. Could you please help me.

I have two files

File1 contains

Col A name of Employee

Col B

Col C

File 2 contains

Col A name of Employee

--------

Col X Reporting Manager Name

Col Y Project Name

 

There are about 2000 records in both the files. I have to take one name from File1 and search the same in file2 and then copy corresponding COL x and COL Y data in file1 COL B and COL C.

I have written the code using Find.

Set FoundCell = Sheets("File2").Cells.Find(what:=MyFind) '' MyFind contains employee name

If Not FoundCell Is Nothing Then
 
    FirstAddress = FoundCell.Address
 
    Rownum = FoundCell.Row
 
    Do
 
     Rownum = FoundCell.Row
 
     Set FoundCell = Sheets("File2").Cells.FindNext(FoundCell)
 
    Loop While Not FoundCell Is Nothing And _
 
     FoundCell.Address <> FirstAddress
 
 
 
    ''''''''''Reporting Manager Name

    Range("X" & Rownum).Select
 
    Selection.Copy
 
    Application.ThisWorkbook.Sheets("File1").Activate
 
    ActiveWorkbook.Sheets("File1").Range("B" & i).PasteSpecial Paste:=xlPasteValues
 
    Application.CutCopyMode = False
 
 
 
    '''''''''Project Name

    Workbooks(strFileName).Activate
 
    Sheets("PES Complete report").Select
 
    Range("Y" & Rownum).Select
 
    Selection.Copy
 
    Application.ThisWorkbook.Sheets("File1").Activate
 
    ActiveWorkbook.Sheets("File1").Range("C" & i).PasteSpecial Paste:=xlPasteValues
 
    Application.CutCopyMode = False
 
 
 
End If
 
Application.ThisWorkbook.Sheets("File1").Activate
 
i = i + 1
 
MyFind = Range("B" & i).Value
 
Wend

But this is taking lots of time.

Could you please help me out with little faster method?

 

‹ Range To Array Prevent Data entry once date has passed ›
»

ScreenUpdating

Submitted by Anonymous on 15 June, 2010 - 17:55.

hi
there are a few things you can do:

1. What's wrong with using Excel's VLOOKUP ?
2. At the start of the code, put:
application.screenupdating = false
then at the end:
application.screenupdating = true

Nick

»

VLookup no idea how to use in VBA

Submitted by megha_joshi on 16 June, 2010 - 05:34.

Dear Nick,

Thank u so much for the answer.

Actually i have not used Vlook up in VBA.

i would appreciate if u help me out.

I have used second tip which u have given. Can we provide some status bar in between?

It still slow. may be Vlook up will help me out

Thank u so much

»
Nick's picture

VLOOKUP

Submitted by Nick on 16 June, 2010 - 19:14.

hi

I was suggesting going away from VBA completely, and just using VLOOKUP.

Using VLOOKUP in VBA is not good.. but if you want to try, use:

    Application.worksheetfunction.Vlookup(xxx)

 

I have one more tip if it's still slow... It could be that you have automatic calculation on, so try putting this at the beginning:

    Application.Calculation = xlCalculationManual

and this at the end:

    Application.Calculation = xlCalculationAutomatic
 

 

Nick

 

»