Given a folder typically containing ~250,000 small (20-100kb) files in HTML format (they open as single-sheet workbooks in Excel) and a list of ~ 1 million filenames, I need to open all the files that match the list and Do Stuff.Overview of Main code Loop: Test 1 Start to Test 1 EndOnce we get to the main file loop, I have a 1-D array arrCompanyNumbers with approx. 1 Million 8-digit company numbers (extracted from a 2-d array arrFilteredAddresses  where the company number is only one of about 12 columns).  There is a folder with a couple hundred thousand files in it named like this. Prod224_0005_00040751_20131231  Prod224_0005_00040789_20130930  .......  That number in the middle is the company number. The number on the end is the file date.The macro loops through the folder using strFilename = dir. For each file, it extracts the company number and tries to match it against the array. If it does, it calls Check_File(). If it doesn't it goes to the next file and repeats.Overview of Check_File()Each file opens as a one-sheet workbook with a corporate accounts filing in it. I want to find the Cash, Assets and Profits in the last year. I have 3 collections of phrases that correspond to those values e.g. Cash at Bank:.  The macro searches the first 200 rows of the first 2 columns for those phrases. Then searches 10 cells across the row and returns the first number it finds.Once it has Cash, Assets and Profits (or failed to find them), it filters them against set criteria. If they pass, it copies the results to a second worksheet in the main workbook (which is eventually just one long list of companies, file dates and cash/assets/profits) and closes the file.Optimisation Parameters:I've already optimised it as far as I think I can e.g. Running speed tests on using vlookup instead of iterative searching. Even after all that, it will typically run for 6-24 hours to filter an entire month of data, and comes dangerously close to running out of memory.  I would like it to run an order of magnitude faster and with a noticeable reduction in memory usage. Any suggestions on how to achieve that would be much appreciated.Runtime Tests:Code between Test 1 Start and Test 1 End consumes approximately 60% of the runtime.The Check_File() sub is responsible for the other 40%Main Sub:Sub Check_Companies_House_Files()Application.ScreenUpdating = FalseApplication.EnableEvents = FalseApplication.Calculation = xlCalculationManual'/================================================================================================================'/ Author: Zak Michael Armstrong'/ Email:  -'/ Date: 07 August 2015'/'/ Summary:'/ Companies House release all the electronic corporate account filings they receive every month, which we download onto our server'/ This spreadsheet takes a list of companies whose registered headquarters are in certain postcodes (The Filtered Addreses)'/ And searches the accounts for these companies' filings (using their company number)'/ It then opens these files (provided in XML or HTML format), and performs a manual search for Cash, Assets and Profits'/ If the level of cash, assets or profits meet set filtering requirements, it copies the spreadsheet into the main book and/or'/ adds the distilled information to a list of account data.'/'/ Speed test results: With the full filtered list (approx. 1M companies), macro can check 5 Companies House Files / second'/=================================================================================================================Dim wbAccountsData              As Workbook         '/ The main Workbook, containing a filtered list of companies to search for and the eventual output dataDim wsFilteredAddresses         As Worksheet        '/ Contains the list of filtered companies with supplementary dataDim wsAccountsData              As Worksheet        '/ Will contain a list of companies and the cash, assets, profits reported on their filingDim arrFilteredAddresses()      As Variant          '/ An array to hold all the data in the filtered addresses spreadsheetDim arrCompanyNumbers()         As Variant          '/ An array to hold all the company numbers (1 column of the filtered addresses data)Dim strRightString              As String           '/ Used to get the company number out of the filenames for comparison with the arrayDim strLeftString               As String           '/Dim strCompanyNumber            As String           '/ Unique identifying number with Companies HouseDim strCompanyName              As String           '/ Company's Registered NameDim strPostcode                 As String           '/ Postcode of their registered addressDim strFileName                 As String           '/ Filename of the company accountsDim strFolderPath               As String           '/ folder the accounts are stored inDim strDay                      As String           '/ the day of the filedateDim strMonth                    As String           '/ the month of the filedateDim strYear                     As String           '/ the year of the filedateDim strFileDate                 As String           '/ the full filedateDim lngFinalRow                 As Long             '/ used for determining size of arraysDim lngFinalColumn              As Long             '/Dim lngCounter                  As Long             '/ used for general countingDim lngCounter2                 As Long             '/Dim lngYear                     As Long             '/ Designates the year to be scanningDim lngMonth                    As Long             '/ Designates the month to be scanning (each folder contains one month)Dim varHolder1                  As Variant          '/ General variable holderDim I                           As Long             '/ General purpose numbersDim J                           As Long             '/Dim K                           As Long             '/Dim L                           As Long             '/Dim M                           As Long             '/Dim N                           As Long             '/Dim lngFolderLength             As Long             '/ Counts the number of files in a folder to be scannedDim lngTriggerPoint             As Long             '/ Used to trigger debug.print operations at set progress intervals'/================================================================================================================='/ Initial Setup'/================================================================================================================= Debug.Print Start:  & Now'/ Remove any residual dataApplication.DisplayAlerts = FalseL = Worksheets.Count    Do While L > 2        Sheets(L).Delete        L = L - 1    LoopApplication.DisplayAlerts = TrueSet wbAccountsData = ActiveWorkbookSet wsFilteredAddresses = Sheets(Filtered Addresses)Set wsAccountsData = Sheets(Accounts Data)    wsFilteredAddresses.Activate'/ Create arrayslngFinalRow = Cells(1048576, 1).End(xlUp).RowIf lngFinalRow = 1 Then lngFinalRow = 1048576lngFinalColumn = Cells(1, 10000).End(xlToLeft).Column    Debug.Print Start array prep:  & Now    ReDim arrFilteredAddresses(1 To lngFinalRow, 1 To lngFinalColumn)        '/ Done iteratively because excel throws an Out of memory if I try to pass the whole range to the array in one go. Approx. 2 minutes for 1Million length list        For L = 1 To lngFinalRow            For M = 1 To lngFinalColumn                arrFilteredAddresses(L, M) = wsFilteredAddresses.Cells(L, M).Text            Next M        Next L    ReDim arrCompanyNumbers(1 To lngFinalRow)        For L = 1 To lngFinalRow            arrCompanyNumbers(L) = Right(00000000 & arrFilteredAddresses(L, 2), 8) '/ company numbers in the filenames are always 8 digits long, with 0's filling up any extra digits        Next L'/ Currently have data from March 2014 to June 2015'/ Currently starts at the most recent and counts backwardlngYear = 2015lngMonth = 6'/================================================================================================================='/ Begin Main loop'/=================================================================================================================Do While lngMonth >= 1 '/ approx. 1M files, should (hopefully) finish over a weekend    lngTriggerPoint = 5000    '/=============================================================    '/ Begin Month Loop    '/=============================================================            Debug.Print lngYear &  -  & MonthName(lngMonth) &  -  & Start file checks:  & Now                strFolderPath = S:\Investments\Data\Companies House\Monthly Companies House Downloads\Accounts_Monthly_Data- & MonthName(lngMonth) & lngYear & \                strFileName = Dir(strFolderPath)            lngFolderLength = 0                Do While strFileName <>                     lngFolderLength = lngFolderLength + 1                    strFileName = Dir                Loop                    '/===============================                    '/ Test 1 start (not including call check_file)                    '/===============================                        strFileName = Dir(strFolderPath)                    lngCounter = 0                    Do While strFileName <>                         lngCounter = lngCounter + 1                        strRightString = Right(strFileName, 22)                        strLeftString = Left(strRightString, 8)                        strCompanyNumber = strLeftString                            K = 1                            '/=============================================================                            '/ Search arrCompanyNumbers for the current file's company                            '/=============================================================                                Do While K <= UBound(arrCompanyNumbers)                                        If strCompanyNumber = arrCompanyNumbers(K) _                                            Then                                                If lngCounter > lngTriggerPoint _                                                    Then                                                        Debug.Print (lngCounter &  -  & lngFolderLength &  -  & Now &  -  & MonthName(lngMonth) &  -  & lngYear)                                                        lngTriggerPoint = lngTriggerPoint + 5000                                                End If                                                strCompanyName = arrFilteredAddresses(K, 1)                                                strPostcode = arrFilteredAddresses(K, 10)                                                strDay = Left(Right(strFileName, 7), 2)                                                strMonth = Left(Right(strFileName, 9), 2)                                                strYear = Left(Right(strFileName, 13), 4)                                                strFileDate = strDay & . & strMonth & . & strYear                                               '/wsFilteredAddresses.Activate       '/ originally introduced to save time by deleting companies from the list as they were found                                               '/wsFilteredAddresses.Rows(K).Delete '/ taken out as not huge time saving, and means only most recent filing is found                                                '/ The subroutine opens the file in question and tries to filter the company against set financial values                                                Call Check_file(strCompanyNumber, strCompanyName, strPostcode, strFileName, strFolderPath, strFileDate, _                                                wbAccountsData, wsAccountsData)                                                DoEvents                                                K = UBound(arrCompanyNumbers) + 1                                        End If                                    K = K + 1                                Loop                        strFileName = Dir                    Loop                    '/===================================                    '/Test 1 End                    '/===================================    '/=============================================================    '/ End Month Loop    '/=============================================================    Debug.Print lngYear &  -  & MonthName(lngMonth) &  -  & Finish:  & Now    Debug.Print Files:  & lngCounter    Debug.Print     lngMonth = lngMonth - 1        If lngMonth <= 0 _            Then                lngMonth = lngMonth + 12                lngYear = lngYear - 1        End If    If lngYear = 2014 And lngMonth = 3 Then lngYear = 2000Loop'/================================================================================================================='/ End Main loop'/=================================================================================================================Debug.Print Macro Finish:  & NowwsAccountsData.ActivateApplication.Calculation = xlCalculationAutomaticApplication.EnableEvents = TrueApplication.ScreenUpdating = TrueEnd SubCheck_File()Private Sub Check_file(ByVal strCompanyNumber As String, ByVal strCompanyName As String, ByVal strPostcode As String, ByVal strFileName As String, ByVal strFolderPath As String, ByVal strFileDate As String, _                        ByRef wbAccountsData As Workbook, ByRef wsAccountsData As Worksheet)'/================================================================================================================'/ Author: Zak Michael Armstrong'/ Email:  -'/'/ Summary:'/ Opens the file, searches the first and second columns for phrases that correspond to cash, assets and profit'/ Searches across the row from those terms until it finds a number'/ It assumes that number is the value it's after'/ If the data meet set filtering requirements, copies the data and/or the worksheet into the main AccountsData workbook'/=================================================================================================================Dim wbTempFile                      As Workbook         '/ This workbook, containing the accounts filingDim wsTempFile                      As Worksheet        '/ This worksheet (always just 1), containing the accounts filingDim arrFirstColumn()                As Variant          '/ The first column of data for text searchDim arrSecondColumn()               As Variant          '/ The second column of data for text searchDim varCash                         As Variant          '/ pre-formatted cash valueDim curCash                         As Currency         '/ currency-formatted cash valueDim varAssets                       As Variant          '/ pre-formatted assets valueDim curAssets                       As Currency         '/ currency-formatted assets valueDim varProfits                      As Variant          '/ pre-formatted profits valueDim curProfits                      As Currency         '/ currency-formatted profits valueDim colCashPhrases                  As Collection       '/ contains all the phrases I've found that correspond to comapnies' current cashDim colAssetPhrases                 As Collection       '/ contains all the phrases I've found that correspond to comapnies' current assetsDim colProfitPhrases                As Collection       '/ contains all the phrases I've found that correspond to comapnies' current profitsDim lngCurrentRow                   As Long             '/ General indicatorsDim lngCurrentColumn                As Long             '/Dim lngFinalRow                     As Long             '/Dim lngFinalColumn                  As Long             '/Dim strPhraseHolder                 As String           '/ will hold a string from a collection for text matchingDim varHolder1                      As Variant          '/ General variable holdersDim varHolder2                      As Variant          '/Dim varHolder3                      As Variant          '/Dim bCashFound                      As Boolean          '/ Checks to see if the program found the valuesDim bAssetsFound                    As Boolean          '/Dim bProfitsFound                   As Boolean          '/Dim bCashFilter                     As Boolean          '/ Is the value going to be used for filteringDim bAssetsFilter                   As Boolean          '/Dim bProfitsFilter                  As Boolean          '/Dim curCashFilterValue              As Currency         '/ the values to set the filter atDim curAssetsFilterValue            As Currency         '/Dim curProfitsFilterValue           As Currency         '/Dim strCashFilterDirection          As String           '/ whether to filter >= or <=Dim strAssetsFilterDirection        As String           '/Dim strProfitsFilterDirection       As String           '/Dim bPassedCashFilter               As Boolean          '/ Handling the (up to) 3 filters separately so these are to check thatDim bPassedAssetsFilter             As Boolean          '/ each filter case has been handled correctlyDim bPassedProfitsFilter            As Boolean          '/Dim I                               As Long             '/ General countersDim J                               As Long             '/Dim K                               As Long             '/Dim L                               As Long             '/Dim M                               As Long             '/Dim N                               As Long             '/'/================================================================================================================='/ Initialise variables, set filter parameters'/=================================================================================================================Workbooks.Open (strFolderPath & strFileName)Set wbTempFile = Workbooks(strFileName)Set wsTempFile = wbTempFile.Sheets(1)bCashFound = FalsebAssetsFound = FalsebProfitsFound = False'/ Column 1 datalngFinalRow = Cells(1048576, 1).End(xlUp).RowReDim Preserve arrFirstColumn(1 To lngFinalRow)For I = 1 To lngFinalRow    arrFirstColumn(I) = UCase(Left(Cells(I, 1).Text, 40)) '/ Left(40) is in case of extremely long cell textNext I'/ Column 2 datalngFinalRow = Cells(1048576, 2).End(xlUp).RowReDim Preserve arrSecondColumn(1 To lngFinalRow)For I = 1 To lngFinalRow    arrSecondColumn(I) = UCase(Left(Cells(I, 2).Text, 40)) '/ Left(40) is in case of extremely long cell textNext I' Fill CollectionsSet colCashPhrases = New CollectioncolCashPhrases.Add (Cash at bank and in hand)colCashPhrases.Add (Cash at bank)colCashPhrases.Add (Cash in hand)colCashPhrases.Add (Cash at bank and in hand:)colCashPhrases.Add (Cash at bank:)colCashPhrases.Add (Cash in hand:)Set colAssetPhrases = New CollectioncolAssetPhrases.Add (Net Current Assets)colAssetPhrases.Add (Total net assets (liabilities))colAssetPhrases.Add (Net Current Assets (liabilities))colAssetPhrases.Add (Total Assets Less current liabilities)colAssetPhrases.Add (Net Current assets/(liabilities))colAssetPhrases.Add (Net Current Assets:)colAssetPhrases.Add (Total net assets (liabilities):)colAssetPhrases.Add (Net Current Assets (liabilities):)colAssetPhrases.Add (Total Assets Less current liabilities:)colAssetPhrases.Add (Net Current assets/(liabilities):)Set colProfitPhrases = New CollectioncolProfitPhrases.Add (Profit and loss account)colProfitPhrases.Add (Profit and loss account:)bCashFilter = FalsebAssetsFilter = FalsebProfitsFilter = TruecurCashFilterValue = 0curAssetsFilterValue = 0curProfitsFilterValue = 250000strCashFilterDirection = >=strAssetsFilterDirection = >=strProfitsFilterDirection = >='/================================================================================================================='/ Search File for Cash, Assets and Profits'/=================================================================================================================On Error Resume Next'/ Search for Cash ValueI = 1    Do While I <= colCashPhrases.Count        strPhraseHolder = UCase(colCashPhrases(I))        varHolder1 = Application.Match(strPhraseHolder, arrFirstColumn, 0)        varHolder2 = UCase(Application.Index(arrFirstColumn, varHolder1))            If IsError(varHolder1) _                Then                varHolder1 = Application.Match(strPhraseHolder, arrSecondColumn, 0)                varHolder2 = UCase(Application.Index(arrSecondColumn, varHolder1))            End If        '/ varholder1 holds the index, varholder2 holds the text value (if found)        If CStr(varHolder2) = strPhraseHolder _            Then                lngCurrentRow = varHolder1                lngCurrentColumn = 1                lngFinalColumn = Cells(lngCurrentRow, 10000).End(xlToLeft).Column                    Do While lngCurrentColumn <= lngFinalColumn                        lngCurrentColumn = lngCurrentColumn + 1                            varCash = Cells(lngCurrentRow, lngCurrentColumn).Value                                If IsNumeric(varCash) And CLng(varCash) <> 0 _                                    Then                                        lngCurrentColumn = lngFinalColumn + 1                                        curCash = CCur(varCash)                                        bCashFound = True                                End If                    Loop        End If        If bCashFound = False Then I = I + 1        If bCashFound = True Then I = colCashPhrases.Count + 1    Loop'/ Search for Assets valueI = 1    Do While I <= colAssetPhrases.Count        strPhraseHolder = UCase(colAssetPhrases(I))        varHolder1 = Application.Match(strPhraseHolder, arrFirstColumn, 0)        varHolder2 = UCase(Application.Index(arrFirstColumn, varHolder1))            If IsError(varHolder1) _                Then                varHolder1 = Application.Match(strPhraseHolder, arrSecondColumn, 0)                varHolder2 = UCase(Application.Index(arrSecondColumn, varHolder1))            End If        '/ varholder1 holds the index, varholder2 holds the text value (if found)        If CStr(varHolder2) = strPhraseHolder _            Then                lngCurrentRow = varHolder1                lngCurrentColumn = 1                lngFinalColumn = Cells(lngCurrentRow, 10000).End(xlToLeft).Column                    Do While lngCurrentColumn <= lngFinalColumn                        lngCurrentColumn = lngCurrentColumn + 1                            varAssets = Cells(lngCurrentRow, lngCurrentColumn).Value                                If IsNumeric(varAssets) And CLng(varAssets) <> 0 _                                    Then                                        lngCurrentColumn = lngFinalColumn + 1                                        curAssets = CCur(varAssets)                                        bAssetsFound = True                                End If                    Loop        End If        If bAssetsFound = False Then I = I + 1        If bAssetsFound = True Then I = colAssetPhrases.Count + 1    Loop'/ Search for profits valueI = 1    Do While I <= colProfitPhrases.Count        strPhraseHolder = UCase(colProfitPhrases(I))        varHolder1 = Application.Match(strPhraseHolder, arrFirstColumn, 0)        varHolder2 = UCase(Application.Index(arrFirstColumn, varHolder1))            If IsError(varHolder1) _                Then                varHolder1 = Application.Match(strPhraseHolder, arrSecondColumn, 0)                varHolder2 = UCase(Application.Index(arrSecondColumn, varHolder1))            End If        '/ varholder1 holds the index, varholder2 holds the text value (if found)        If CStr(varHolder2) = strPhraseHolder _            Then                lngCurrentRow = varHolder1                lngCurrentColumn = 1                lngFinalColumn = Cells(lngCurrentRow, 10000).End(xlToLeft).Column                    Do While lngCurrentColumn <= lngFinalColumn                        lngCurrentColumn = lngCurrentColumn + 1                            varProfits = Cells(lngCurrentRow, lngCurrentColumn).Value                                If IsNumeric(varProfits) And CLng(varProfits) <> 0 _                                    Then                                        lngCurrentColumn = lngFinalColumn + 1                                        curProfits = CCur(varProfits)                                        bProfitsFound = True                                End If                    Loop        End If        If bProfitsFound = False Then I = I + 1        If bProfitsFound = True Then I = colProfitPhrases.Count + 1    Loop    On Error GoTo 0'/================================================================================================================='/ Determine filter outcome'/=================================================================================================================bPassedCashFilter = FalsebPassedAssetsFilter = FalsebPassedProfitsFilter = False'/ Filter CashIf bCashFilter = True _    Then        Select Case strCashFilterDirection            Case Is = >=                If curCash >= curCashFilterValue Then bPassedCashFilter = True Else bPassedCashFilter = False            Case Is = <=                If curCash <= curCashFilterValue Then bPassedCashFilter = True Else bPassedCashFilter = False            Case Else                MsgBox (Macro encountered an unexpected error whilst filtering file financial data)                Stop        End Select    Else        bPassedCashFilter = TrueEnd If'/ Filter AssetsIf bAssetsFilter = True _    Then        Select Case strAssetsFilterDirection            Case Is = >=                If curAssets >= curAssetsFilterValue Then bPassedAssetsFilter = True Else bPassedAssetsFilter = False            Case Is = <=                If curAssets <= curAssetsFilterValue Then bPassedAssetsFilter = True Else bPassedAssetsFilter = False            Case Else                MsgBox (Macro encountered an unexpected error whilst filtering file financial data)                Stop        End Select    Else        bPassedAssetsFilter = TrueEnd If'/ Filter ProfitsIf bProfitsFilter = True _    Then        Select Case strProfitsFilterDirection            Case Is = >=                If curProfits >= curProfitsFilterValue Then bPassedProfitsFilter = True Else bPassedProfitsFilter = False            Case Is = <=                If curProfits <= curProfitsFilterValue Then bPassedProfitsFilter = True Else bPassedProfitsFilter = False            Case Else                MsgBox (Macro encountered an unexpected error whilst filtering file financial data)                Stop        End Select    Else        bPassedProfitsFilter = TrueEnd If'/ The filter might return true against a default value of 0 if real number not found, so fail if real number not foundIf bCashFound = False And bCashFilter = True Then bPassedCashFilter = FalseIf bAssetsFound = False And bAssetsFilter = True Then bPassedAssetsFilter = FalseIf bProfitsFound = False And bProfitsFilter = True Then bPassedProfitsFilter = False'/ if passed all 3 conditions, then print and/or copy to main workbookIf bPassedCashFilter = True And bPassedAssetsFilter = True And bPassedProfitsFilter = True _    Then        wbAccountsData.Activate        wsAccountsData.Activate        lngFinalRow = Cells(1048576, 2).End(xlUp).Row        lngCurrentRow = lngFinalRow + 1        Cells(lngCurrentRow, 2) = strCompanyNumber        Cells(lngCurrentRow, 3) = strCompanyName        Cells(lngCurrentRow, 4) = strPostcode        Cells(lngCurrentRow, 5) = curCash        Cells(lngCurrentRow, 6) = curAssets        Cells(lngCurrentRow, 7) = curProfits        Cells(lngCurrentRow, 8) = strFileDate''        '/ copies worksheet to main workbook''''        wbTempFile.Activate''        wsTempFile.Copy After:=wbAccountsData.Worksheets(wbAccountsData.Worksheets.Count)''        wbAccountsData.Activate''        ActiveSheet.Name = strCompanyNumber &  -  & strFileDateEnd IfwbAccountsData.ActivatewbTempFile.CloseEnd Sub
Matching between files and a list of filenames at scale
performance;file system;vba;excel
Here are a couple of things to look into:In the Check_Companies_House_Files, near the start you have this block of code:ReDim arrFilteredAddresses(1 To lngFinalRow, 1 To lngFinalColumn)'/ Done iteratively because excel throws an Out of memory if I try to pass the whole range to the array in one go. Approx. 2 minutes for 1Million length listFor L = 1 To lngFinalRow    For M = 1 To lngFinalColumn        arrFilteredAddresses(L, M) = wsFilteredAddresses.Cells(L, M).Text    Next MNext LReDim arrCompanyNumbers(1 To lngFinalRow)For L = 1 To lngFinalRow    arrCompanyNumbers(L) = Right(00000000 & arrFilteredAddresses(L, 2), 8) '/ company numbers in the filenames are always 8 digits long, with 0's filling up any extra digitsNext LWhy have two separate loops? You should be able to merge them.ReDim arrFilteredAddresses(1 To lngFinalRow, 1 To lngFinalColumn)ReDim arrCompanyNumbers(1 To lngFinalRow)'/ Done iteratively because excel throws an Out of memory if I try to pass the whole range to the array in one go. Approx. 2 minutes for 1Million length listFor L = 1 To lngFinalRow    For M = 1 To lngFinalColumn        arrFilteredAddresses(L, M) = wsFilteredAddresses.Cells(L, M).Text    Next M    arrCompanyNumbers(L) = Right(00000000 & arrFilteredAddresses(L, 2), 8) '/ company numbers in the filenames are always 8 digits long, with 0's filling up any extra digitsNext LWhen you build the arrCompanyNumbers array, you do that text formatting 1 million times. Why not add a column to your Excel sheet with the value already formatted using the TEXT function? You then just read that column into the array.Your code is looping through your array arrCompanyNumbers looking for a matching entry. You might to look into using a Scripting.Dictionary instead because the Exists method is possibly much quicker than a 1 million long array. Presumably your company number values are all unique?If your files are HTML, Excel probably spends a lot of time parsing the HTML code when opening the file. You could try using the Scripting.FileSystemObject and the Scripting.TextStream.ReadAll method. This will load the file into a string variable and you could then use the InStr function to search for your text entries. If the HTML is complicated/fussy it might be too tricky to find the value that goes with your heading.You do this kind of thing a couple of times:    varHolder1 = Application.Match(strPhraseHolder, arrFirstColumn, 0)    varHolder2 = UCase(Application.Index(arrFirstColumn, varHolder1))    If IsError(varHolder1) _        Then        varHolder1 = Application.Match(strPhraseHolder, arrSecondColumn, 0)        varHolder2 = UCase(Application.Index(arrSecondColumn, varHolder1))    End IfI've got two issues with this. First, why bother getting the value for varHolder2 before you've checked if varHolder1 is an error? You should check varHolder1 first and then decide what to do. (By the way, why do you put the Then onto a new line and indent it? I would leave it on the same line as the If unless the line is very long.)    varHolder1 = Application.Match(strPhraseHolder, arrFirstColumn, 0)    If Not IsError(varHolder1) Then        varHolder2 = UCase(Application.Index(arrFirstColumn, varHolder1))    Else        varHolder1 = Application.Match(strPhraseHolder, arrSecondColumn, 0)        varHolder2 = UCase(Application.Index(arrSecondColumn, varHolder1))    End IfThe second thing, is why use the Index function at all? The Match function has told you the row number/index number of the element. Why not use:varHolder2 = UCase(arrSecondColumn(varHolder1))A minor niggle is you hardcoding the max number of rows in your code, e.g. lngFinalRow = Cells(1048576, 1).End(xlUp).Row. This is fragile and might break if you upgrade Excel. Better to use lngFinalRow = Cells(wsAccountsData.Rows.Count, 1).End(xlUp).Row