Optimization Issue with Vlookup UDF

[otictac1]

Hello everyone,

I am having an issue with optimization and I was wondering if anyone had any suggestions they could throw out to speed my code up. I need to run a Vlookup against 4 massively large spreadsheets. I am using Ozgrid's UDF found here:

Vlookup. Excel Vlookup Across Excel Worksheets. Custom VLookup Formula/Function

I am using this as part of a larger VBA script that vlookups up certain numbers. The thing to be found will only appear once on one of the four worksheets and the worksheets are in alphabetically order. Each worksheet that I am vlooking up against has 65k or so rows.

Can anyone think of a way to optimize this so that it doesn't take so long for the VBA script to run?

Here is the snippet of code where this is used:

Do

        ActiveCell.FormulaR1C1 = _
        "=PERSONAL.XLSB!VLOOKAllSheets(CONCATENATE(Sheet2!R[" & row & "]C[-2],MID(RC[4],LEN(RC[4])-1,1),RIGHT(RC[2],1)),C[-1]:C[0],2,FALSE)"
        
        row = row - 1
   
   ActiveCell.Offset(1, 0).Select   
   
    If Right(ActiveCell.Offset(0, 2), 1) = 1 Then
    row = row + 1
    End If  
   
   
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))

 

[wigi]

Hi

The obvious recommendation is to turn off calculations at the beginning of the macro and turn on back again after the meat of the macro is done.

But next to that, note that looping is very inefficient in Excel VBA. You need to write formulas or values in cells in only 1 write operation. Not using a loop and writing one formula at a time.

 

[otictac1]

Hi

The obvious recommendation is to turn off calculations at the beginning of the macro and turn on back again after the meat of the macro is done.

But next to that, note that looping is very inefficient in Excel VBA. You need to write formulas or values in cells in only 1 write operation. Not using a loop and writing one formula at a time.

I turned off auto calculations and it took my time down from 109 seconds to 17 seconds so that is a huge improvement. However, I would like to ask you about the no looping thing. I can't really figure out a way to get that formula in without looping. I need some of the places where the Concatenate to change to be done based upon what row it is in, but not in a predictable way. Can you give any suggestions as to how to get this out of a loop?

 

[wigi]

Hi there

Talking in pseudo-code, you could have an extra (temporary) column containing the logic for your "row".
Then use that column in applying your formula to the entire range of cells.
Lastly, delete the extra column again. That extra column will have formula (temporarily since you clear out the column when it's not needed anymore).