do vlookups in loops slow things down? IF so how to speed up

[miless2111s]

Background:
I have a complex worksheet that I am turning into code so that I can attempt to protect the calculations with VBA obscuring. This has resulted in a macro which loops around 1000 times and has a significant number of vlookups built in. It gathers information from the looks up, performs calculations as required and then outputs the results to the display sheet which has c. 1000 rows in at the moment however this will eventually grow to around 2000.

The vlookups are looking into 5 or more distinct tables of data:
Dates 2x2000 columns x rows
Rows 2x80
Data 10x 250
Colours 12 x possibly lots but usually less than 50
Definitions 10 x400I

Issue:
Running the code works a charm and does the job however it takes rather a long time to run (around a minute) and so far I've only dealt with 50% of the calculations. As I will be setting the macro to run when the workbook is closed and saved this delay will be uncomfortable and slows the whole process down.

Help needed:
Is there some way to either speed up the Vlookup process or a technique to pre-load all the information into the code and then the code uses something other than vlookup to fetch the required information?

Many thanks

Miles

 

[HackSlash]

There are 2 things I know of that will help:

1. Sorting the data will make your lookups faster
2. The double VLOOKUP trick:

https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

 

[miless2111s]

OK, I made the changes to my test code block and with the range.cells command it averaged at 0.3294271 across 5 goes, so quicker than the offset method by a good way and marginally quicker than the vlookup (though that will sum up quite quickly I suspect)

Now to have a read and understand of your array stuff

 

[miless2111s]

Both sets of data are sorted by Vref, the first by a pivot table and the second because the user doesn't have a choice so that option is already in play. The double vlookup does sound interesting, especially in the situation where I haven't actually used all the vrefs. I suspect that should I have 200 things kicking around then the speed difference will be zero as I will need to do the slower FALSE lookup but where I do have blanks it can quickly eliminate these - using this in the first error checking situation will allow the code to quickly dump out a "missing" value, thanks for that.

Miles

 

[miless2111s]

Miles

You can store a whole range in a 2 dimensional range, for example.

Dim MS_Data As Range
Dim arrMS_Data As Variant

    Set MS_Data = Range("MS_Data")

    arrMS_Data = MS_Data.Value

You would still use Match on that array so there would be no gain there but when it comes to actually getting the values you want pulling them from the array should be quicker than from a range on a worksheet.

Let's say you did have the code above then the code we've both been working on might look something like this.

               Res = Application.Match(vref, Application.Index(arrMS_Data, 1), 0)
                
                ItemType = ""    ' reset item type just in case

                Start = arrMS_Data(Res, 2) ' Application.VLookup(Vref, MS_Data, 2, False)
                If Start < SumamryStart Then Start = SumamryStart    'sets the start to the first date in the summary if it is before (makes plotting easier)

                finish = arrMS_Data(Res, 3) ' Application.VLookup(Vref, MS_Data, 3, False)
                If finish > SummaryEnd Then finish = SummaryEnd    'sets the finish to the last date in the summary if it is before (makes plotting easier)

                BLStart = arrMS_Data(Res, 4) ' Application.VLookup(Vref, MS_Data, 4, False)
                If BLStart < SumamryStart Then BLStart = SumamryStart    'sets the start to the first date in the summary if it is before (makes plotting easier)

                BLFinish = arrMS_Data(Res, 5) ' Application.VLookup(Vref, MS_Data, 5, False)
                If BLFinish > SummaryEnd Then BLFinish = SummaryEnd    'sets the finish to the last date in the summary if it is before (makes plotting easier)

                Complete = arrMS_Data(Res, 6) ' Application.VLookup(Vref, MS_Data, 6, False)

                Slack = arrMS_Data(Res, 7) ' Application.VLookup(Vref, MS_Data, 7, False)

                RAG = arrMS_Data(Res, 8) ' Application.VLookup(Vref, MS_Data, 8, False)
                
                If arrMS_Data(Res, 9) Then ' Application.VLookup(Vref, MS_Data, 9, False) = "y" Then
                    MS = "y"
                Else
                    MS = "n"
                End If
                
                Critical = arrMS_Data(Res, 11) 'Application.VLookup(Vref, MS_Data, 11, False)

Of course that is totally untested for either errors or speed.

OK, that's not working for some reason; the "MS_Data_Line = Application.Match(Vref, Application.Index(MS_Data_Array, 1), 0)" line is throwing a Type Mismatch - what should MS_Data_line be declared as? At the moment I've got it as a string... (integer doesn't work either)

Sub Test_Array_match_and_cell()
'test place to see how Match and array will work
Dim Start As Date
Dim V_Row As String
Dim MS_Data As Range
Dim Bar_data As Range
Dim Vref As Integer
Dim t As Single
t = Timer
Dim MS_Data_Line_line As String
Dim MS_Data_index As Range
Dim MS_Data_Origin As Range
Dim Bar_data_index As Range
Dim Bar_data_origin As Range
Dim Bar_Data_Line As String
Dim MS_Data_Array As Variant

Worksheets("MS Project Bars Import").Activate ' this doesn't appear to be working
'Set MS_Data = Range("$AX:$BH")
Set MS_Data = Range("MS_Data") 'why is this not picking up the range???
MS_Data_Array = MS_Data.Value
'Set MS_Data = ActiveWorkbook.Names("MS_Data").RefersToRange
'Set MS_Data_index = Range("MS_Data").Columns(1)
'Set MS_Data_Origin = Range(Range("MS_Data").Cells(1, 1).Address(0, 0))
Worksheets("Bar Details").Activate
Set Bar_data = Range("Bar_data")
'Set Bar_data_index = Range("Bar_data").Columns(1)
'Set Bar_data_origin = Range(Range("Bar_data").Cells(1, 1).Address(0, 0))
'Debug.Print "MS_Data_Origin: " & MS_Data_Origin.Value
'Debug.Print "Bar_Data_Origin: " & Bar_data_origin.Value
For Vref = 1 To 200
    If IsError(Application.Match(Vref, MS_Data.Columns(1), 0)) Or IsError(Application.Match(Vref, Bar_data.Columns(1), 0)) Then 'checks if the VRef is used in either the plan or the bar_details sheet to avoid issues with the vlookups
        ItemType = "BLANK_Main"
        Debug.Print "Ref: " & Vref & " no data"
    Else
'                      Application.Match(vref, Application.Index(arrMS_Data, 1), 0)
        MS_Data_Line = Application.Match(Vref, Application.Index(MS_Data_Array, 1), 0) ' finds the row for VRef
        Start = MS_Data_Array(MS_Data_Line, 2) 'MS_Data_Origin.Offset(MS_Data_Line, 1).Value
        finish = MS_Data_Array(MS_Data_Line, 3) 'MS_Data_Origin.Offset(MS_Data_Line, 2).Value
        BLStart = MS_Data_Array(MS_Data_Line, 4) 'MS_Data_Origin.Offset(MS_Data_Line, 3).Value
        BLFinish = MS_Data_Array(MS_Data_Line, 5) 'MS_Data_Origin.Offset(MS_Data_Line, 4).Value
        Complete = MS_Data_Array(MS_Data_Line, 6) 'MS_Data_Origin.Offset(MS_Data_Line, 5).Value
        Slack = MS_Data_Array(MS_Data_Line, 7) 'MS_Data_Origin.Offset(MS_Data_Line, 6).Value
        RAG = MS_Data_Array(MS_Data_Line, 8) 'MS_Data_Origin.Offset(MS_Data_Line, 7).Value
        MS = MS_Data_Array(MS_Data_Line, 9) 'MS_Data_Origin.Offset(MS_Data_Line, 8).Value
        Critical = MS_Data_Array(MS_Data_Line, 11) 'MS_Data_Origin.Offset(MS_Data_Line, 10).Value
        Debug.Print "MS_Data: "
        Debug.Print "Ref: " & Vref & " line: " & MS_Data_Line & "start: " & Start & " " & finish & " " & BLStart & " " & BLFinish & " " & Complete & " " & Slack & " " & RAG & " " & MS & " " & Critical
        
        Bar_Data_Line = Application.Match(Vref, Bar_data.Columns(1), 0)
        V_Row = Bar_data.Cells(Bar_Data_Line, 6) 'Bar_data_origin.Offset(Bar_Data_Line, 5).Value
        Hidden_name = Bar_data.Cells(Bar_Data_Line, 2) 'Bar_data_origin.Offset(Bar_Data_Line, 1).Value
        Display_name = Bar_data.Cells(Bar_Data_Line, 3) 'Bar_data_origin.Offset(Bar_Data_Line, 2).Value
        V_Row_number = Bar_data.Cells(Bar_Data_Line, 4) 'Bar_data_origin.Offset(Bar_Data_Line, 3).Value
        Bar_colour = Bar_data.Cells(Bar_Data_Line, 5) 'Bar_data_origin.Offset(Bar_Data_Line, 4).Value
        AB = Bar_data.Cells(Bar_Data_Line, 7) 'Bar_data_origin.Offset(Bar_Data_Line, 6).Value
        Height_mod = Bar_data.Cells(Bar_Data_Line, 8) 'Bar_data_origin.Offset(Bar_Data_Line, 7).Value
        Debug.Print "Bar_data: "
        Debug.Print "Ref: " & Vref & " line: " & Bar_Data_Line & " Y axis " & V_Row & " " & Hidden_anem & " " & Display_name & " " & V_Row_number & " " & Bar_colour & " " & AB & " " & Height_mod
    End If
    'Debug.Print "Ref: " & Vref & ":" & "line: " & line & ": " & Start & " - " & V_Row
Next Vref
MsgBox Timer - t
End Sub

 

[Norie]

You should declare MS_Data_line as Variant or Long, preferably the former as that will allow the variable to store error values which you can then check for later in the code.

As for the problem I think it's because I didn't get the Index formula quite right.

Try changing the declaration and using this adjusted code.

MS_Data_Line = Application.Match(Vref, Application.Index(MS_Data_Array, ,1), 0) ' finds the row for VRef

If that doesn't work we might be looking at sticking the first column of MS_Data_Array into its own array, think you had something similar in earlier code when still using ranges.

 

[miless2111s]

You should declare MS_Data_line as Variant or Long, preferably the former as that will allow the variable to store error values which you can then check for later in the code.

As for the problem I think it's because I didn't get the Index formula quite right.

Try changing the declaration and using this adjusted code.

MS_Data_Line = Application.Match(Vref, Application.Index(MS_Data_Array, ,1), 0) ' finds the row for VRef

If that doesn't work we might be looking at sticking the first column of MS_Data_Array into its own array, think you had something similar in earlier code when still using ranges.

Cor! that's a subtle change Sadly it didn't work Run time error '13' Type Mismatch. This happens if the range_Line items are declared as Variants or Long

 

[Norie]

I think we might need to put the first column of data in its own array, I was trying to use Index to extract that column and it appeared to work in my simple test setup.

Can you post all the current code?

 

[miless2111s]

I think we might need to put the first column of data in its own array, I was trying to use Index to extract that column and it appeared to work in my simple test setup.

Can you post all the current code?

Sub Test_Array_match_and_cell()
'test place to see how Match and array will work
Dim Start As Date
Dim V_Row As String
Dim MS_Data As Range
Dim Bar_data As Range
Dim Vref As Integer
Dim t As Single
t = Timer
Dim MS_Data_Line As Long
Dim MS_Data_index As Range
Dim MS_Data_Origin As Range
Dim Bar_data_index As Range
Dim Bar_data_origin As Range
Dim Bar_Data_Line As Long
Dim MS_Data_Array As Variant

Worksheets("MS Project Bars Import").Activate ' this doesn't appear to be working
'Set MS_Data = Range("$AX:$BH")
Set MS_Data = Range("MS_Data") 'why is this not picking up the range???
MS_Data_Array = MS_Data.Value
'Set MS_Data = ActiveWorkbook.Names("MS_Data").RefersToRange
'Set MS_Data_index = Range("MS_Data").Columns(1)
'Set MS_Data_Origin = Range(Range("MS_Data").Cells(1, 1).Address(0, 0))
Worksheets("Bar Details").Activate
Set Bar_data = Range("Bar_data")
'Set Bar_data_index = Range("Bar_data").Columns(1)
'Set Bar_data_origin = Range(Range("Bar_data").Cells(1, 1).Address(0, 0))
'Debug.Print "MS_Data_Origin: " & MS_Data_Origin.Value
'Debug.Print "Bar_Data_Origin: " & Bar_data_origin.Value
For Vref = 1 To 200
    If IsError(Application.Match(Vref, MS_Data.Columns(1), 0)) Or IsError(Application.Match(Vref, Bar_data.Columns(1), 0)) Then 'checks if the VRef is used in either the plan or the bar_details sheet to avoid issues with the vlookups
        ItemType = "BLANK_Main"
        Debug.Print "Ref: " & Vref & " no data"
    Else
        MS_Data_Line = Application.Match(Vref, Application.Index(MS_Data_Array, , 1), 0) ' finds the row for VRef
        Start = MS_Data_Array(MS_Data_Line, 2) 'MS_Data_Origin.Offset(MS_Data_Line, 1).Value
        finish = MS_Data_Array(MS_Data_Line, 3) 'MS_Data_Origin.Offset(MS_Data_Line, 2).Value
        BLStart = MS_Data_Array(MS_Data_Line, 4) 'MS_Data_Origin.Offset(MS_Data_Line, 3).Value
        BLFinish = MS_Data_Array(MS_Data_Line, 5) 'MS_Data_Origin.Offset(MS_Data_Line, 4).Value
        Complete = MS_Data_Array(MS_Data_Line, 6) 'MS_Data_Origin.Offset(MS_Data_Line, 5).Value
        Slack = MS_Data_Array(MS_Data_Line, 7) 'MS_Data_Origin.Offset(MS_Data_Line, 6).Value
        RAG = MS_Data_Array(MS_Data_Line, 8) 'MS_Data_Origin.Offset(MS_Data_Line, 7).Value
        MS = MS_Data_Array(MS_Data_Line, 9) 'MS_Data_Origin.Offset(MS_Data_Line, 8).Value
        Critical = MS_Data_Array(MS_Data_Line, 11) 'MS_Data_Origin.Offset(MS_Data_Line, 10).Value
        Debug.Print "MS_Data: "
        Debug.Print "Ref: " & Vref & " line: " & MS_Data_Line & "start: " & Start & " " & finish & " " & BLStart & " " & BLFinish & " " & Complete & " " & Slack & " " & RAG & " " & MS & " " & Critical
        
        Bar_Data_Line = Application.Match(Vref, Bar_data.Columns(1), 0)
        V_Row = Bar_data.Cells(Bar_Data_Line, 6) 'Bar_data_origin.Offset(Bar_Data_Line, 5).Value
        Hidden_name = Bar_data.Cells(Bar_Data_Line, 2) 'Bar_data_origin.Offset(Bar_Data_Line, 1).Value
        Display_name = Bar_data.Cells(Bar_Data_Line, 3) 'Bar_data_origin.Offset(Bar_Data_Line, 2).Value
        V_Row_number = Bar_data.Cells(Bar_Data_Line, 4) 'Bar_data_origin.Offset(Bar_Data_Line, 3).Value
        Bar_colour = Bar_data.Cells(Bar_Data_Line, 5) 'Bar_data_origin.Offset(Bar_Data_Line, 4).Value
        AB = Bar_data.Cells(Bar_Data_Line, 7) 'Bar_data_origin.Offset(Bar_Data_Line, 6).Value
        Height_mod = Bar_data.Cells(Bar_Data_Line, 8) 'Bar_data_origin.Offset(Bar_Data_Line, 7).Value
        Debug.Print "Bar_data: "
        Debug.Print "Ref: " & Vref & " line: " & Bar_Data_Line & " Y axis " & V_Row & " " & Hidden_anem & " " & Display_name & " " & V_Row_number & " " & Bar_colour & " " & AB & " " & Height_mod
    End If
    'Debug.Print "Ref: " & Vref & ":" & "line: " & line & ": " & Start & " - " & V_Row
Next Vref
MsgBox Timer - t
End Sub

 

[miless2111s]

interestingly I put the worksheet("XXXX"). in front of all the range statements and it has started working however it is dog slow! The other methods less than a split second to run 0.005 - 0.007 but the array method s taking 26+ seconds to do the same sort of loop So something is wrong there

 

[Aladin Akyurek]

interestingly I put the worksheet("XXXX"). in front of all the range statements and it has started working however it is dog slow! The other methods less than a split second to run 0.005 - 0.007 but the array method s taking 26+ seconds to do the same sort of loop So something is wrong there

Is your table sorted in ascending order? If not, are you allowed to do so and keep it in sorted order? If the answer yes, we should able to run a native look up formula that is confortably fast...