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?
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))
Application.CutCopyMode = False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _