[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

VBA Excel - Outer file Vlookup speed issues

Dear Experts,

Can I ask your experience about known performance issues if with VBA would like to vlookup another excel file.

In the attached Code part I am linking and outer file and that file is always around 3 Mb in size with around 3500 rows and 54 columns.Basically the source file so from where the vlookup macro is starting has the same parameters.

Somehow this vlookup is very slow, I am sure it depends on the vlookup range because if I change the target range from R1C12:R3500C12 to R1C12:R15C12 so only just checking 15 rows, it runs in some seconds.

My concrete questions would be
- does it matter whether the outer file is open, or better to leave it close?
- generally such vlookup used to be applied between files, or there are such disadvantages?
- do you have ideas how to be in balance and speed it up?

thanks,
Range("AU2").Select
ActiveCell.FormulaR1C1 = _
            "=IF(ISNA(VLOOKUP(RC[-35],'D:\Path\" & MilyenHonap & "\[OpenOrders " & _
           FileNap & ".03.10.xls]Sheet1'!R1C12:R3500C12,1,0)),""Go"","""")"
Range("AU2").Copy Range(Cells(3, 47), Cells(LastRowWithValue, 47))
Columns("AU:AU").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Open in new window

0
csehz
Asked:
csehz
  • 2
  • 2
1 Solution
 
Rory ArchibaldCommented:
It would be better if the other file is open.
0
 
csehzIT consultantAuthor Commented:
Yes I tried it is much better.. Interesting why but definitely.

Can I ask if we have D:\Path\File.xls, how to open it through VBA?

0
 
Rory ArchibaldCommented:
The code would be:

Workbooks.Open "D:\Path\File.xls"

Open in new window

0
 
csehzIT consultantAuthor Commented:
Thanks very much I was not aware concretely
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now