Excel VBA, Can you use Index, Match in VBA without calling Worksheet.Function?

Posted on 2013-10-17
Medium Priority
Last Modified: 2013-10-18
I need to loop through an array, do an index match for tens of thousands of records and the call to worksheet.function is extremely slow. Any way to avoid the slowness or the call altogether?

        Set rTradeId = .Range("B2:B" & lRowsEag)
        avTradeId = rTradeId.Value2
        Set rEntity = .Range("C2:C" & lRowsEag)
        avEntity = rEntity.Value2
        On Error Resume Next
            With WorksheetFunction
                For j = 1 To UBound(avEntity)
                    avEntity(j, 1) = .Index(rTableLch, .Match(avTradeId(j, 1), rShAdmin, 0), 1)
                Next j
            End With
        On Error GoTo 0
        rEntity.Value2 = avEntity
Question by:newparadigmz
LVL 29

Assisted Solution

leonstryker earned 750 total points
ID: 39580428
Not really. It is slow because of the number of functions you are executing.
LVL 39

Accepted Solution

nutsch earned 750 total points
ID: 39580432
You could add a column, put the formula in it (through VBA), then copy / paste value and be done. It might be faster.

Have you added the classic speed up tags around your code?

With application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

'your code

With application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With

Open in new window


Author Closing Comment

ID: 39583874
That's a bummer, Autofill is faster, but still pretty slow.


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question