Suppressing external link update when entering a string as a formula

[JohnOrtman]

Excel Version 2010.

Executive comment: The pasting of my links takes to long to generate my consolidation file since they are updating as I go.

I have built a routine that creates a tab that consolidates data from ~60 template files (all identical except are different general ledger cost centers). A few of the source files have more than one cost center, but my code accounts for that and is irrelevant to this issue.

My tab's first column is empty. My tab's source column is the source filename/tab name. The third column is the concatenated Entity/Cost Center/General ledger account (the index). There are 270 general ledger accounts for each entity/cost center (again, there are ~60) so there are ultimately ~16200 rows. My cross-tab data after the pasting of the link starts at the fourth column and carries 12 columns to the right (January through December budget numbers). The VLOOKUP references for the columns (Jan through Dec) run across cells D2:O2 (the columns may be 18 through 29...not sure until template design done)

The purpose of the tab is to convert it when done to a .txt file for loading the budget into our system.

The source file's first column has the entity/cost center/GL index that the results tab has for a vlookup.

My problem is that when entering each vlookup formula, Excel wants to generate the result for each cell. This results in Excel taking 20 minutes for each cost center, and ultimately 20 hours for all cost centers.

I have tried changing the calculations to manual, and also turning off screen updating with no change in the execution time.

My question is: Is there a way for me to suppress the linking of the files and getting the results until all the formulas are pasted and then do the updating? When one source file is open, the routine works instantly if it's limited to just the first cost center. However, opening all the source files is unfeasible given the size of the 60 files.

Note that the code below is only for the first cost center as that is the template that is under development. When it is approved and we replicated it to the other ~60 cost centers, I would add another For 1 to 60 loop to run through all the files.

/code

Sub PasteVlook()

Application.ScreenUpdating = False

Const FolderPath As String = "'S:\Fin-Rep\Corporate Analytics\Budget\2016 Budget\Planning\"
Const Comma As String = ","
Const NamedRange As String = "UploadRange"
Const PeriodRow As Integer = 2

Dim Period As String
Dim PeriodColumn As Integer
Dim GLCount As Integer
Dim FileTab As String
Dim TargetCell As Range
Dim LineCounter As Integer
Dim ColumnCounter As Integer


Set TargetCell = Worksheets(Extract).Range("D3")

For LineCounter = 1 To 270
FileTab = TargetCell.Offset(, -2).Value
GLCount = 1
PeriodColumn = 4
For ColumnCounter = 1 To 12
TargetCell.FormulaR1C1 = "=Vlookup(R[0]C[" & -GLCount & "]" & Comma & FolderPath & FileTab & Comma & "R" & _
PeriodRow & "C" & PeriodColumn & Comma & "False)"
Set TargetCell = TargetCell.Offset(, 1)
GLCount = GLCount + 1
PeriodColumn = PeriodColumn + 1
Next ColumnCounter
Set TargetCell = TargetCell.Offset(1, -12)
Next LineCounter

Application.ScreenUpdating = True

MsgBox ("Done")

End Sub

/End code

Thanks for your thoughts in advance. Note that designing an array filled with all the formulas and pasting at one time is beyond my skillset at this time, so if to you that's the obvious solution keep my ignorance in mind.

As an aside, the reason I'm using code at all and not manually linking each file is because there may be additional cost centers and GL accounts as we go through budgeting and inserting new GL accounts accurately is error prone and not a good long term solution (we intend to use this for future forecasts and budgets)

I'm afraid I can't attach the file due to corp security issues.

 

[RedBeard]

As you've noted, vlookup is painfully slow. You also write that designing an array is not within your skillset right now, but have you considered INDEX/MATCH? It is a faster option.

 

[JohnOrtman]

No I had not considered that. I don't believe it is the vlookup that is the issue so much as it is the linking cell by individual cell. At first blush it seems INDEX/MATCH would run into the same issue. Still seeking if there is way to stop my file from accessing source file till end of routine