Efficient alternatives in VBA to vlookup or index/match?

[Eric4545]

Hi there,

I'm having an issue related to how long a macro takes to complete. Originally my code used a significant number of WorksheetFunction.Vlookup formulas to pull the data I required into my Allocation Sheet.

After doing some reading/testing it seems that my code below is now a slight improvement as I'm only using the match function once, then taking advantage of a relatively fast index formula. However, this didn't see the significant improvement in speed I was hoping to achieve. It feels like someone with more experience in VBA would be able to improve this code dramatically. Any help or suggestions are greatly appreciated.

Please note:
1) I am already turning off calculations, display alerts, screen updating etc. although I didn't include it in the code below.
2) Below is just a small subset of a larger macro, but this (along with other lookups) appears to be where my code efficiency suffers
3) My Allocation sheet has approximately 32,000 rows, the Sls sheet (sheet2) has approximately 250,000 rows

Sub Data_Inputs()
Dim LastRow_All, LastRow_Sls, i As Long
Dim Sls_rng As Range


LastRow_All = Sheets("Allocation").Cells.SpecialCells(xlCellTypeLastCell).Row
LastRow_Sls = Sheet2.Cells.SpecialCells(xlCellTypeLastCell).Row
Set Sls_rng = Sheet2.Range("B2:B" & LastRow_Sls)


Sheets("Allocation").Range("AJ1").Value = "Match Sls"


On Error Resume Next
For i = LastRow_All To 2 Step -1


Sheets("Allocation").Cells(i, 36) = Application.WorksheetFunction.Match(Cells(i, 1), Sls_rng.Offset(0, -1), 0)


Sheets("Allocation").Cells(i, 26) = Application.WorksheetFunction.Index(Sls_rng.Offset(0, 2), Cells(i, 36))
Sheets("Allocation").Cells(i, 27) = Application.WorksheetFunction.Index(Sls_rng.Offset(0, 3), Cells(i, 36))
Sheets("Allocation").Cells(i, 28) = Application.WorksheetFunction.Index(Sls_rng.Offset(0, 4), Cells(i, 36))
Sheets("Allocation").Cells(i, 29) = Application.WorksheetFunction.Index(Sls_rng.Offset(0, 5), Cells(i, 36))
Sheets("Allocation").Cells(i, 30) = Application.WorksheetFunction.Index(Sls_rng.Offset(0, 6), Cells(i, 36))
Sheets("Allocation").Cells(i, 31) = Application.WorksheetFunction.Index(Sls_rng.Offset(0, 7), Cells(i, 36))
Sheets("Allocation").Cells(i, 32) = Application.WorksheetFunction.Index(Sls_rng.Offset(0, 8), Cells(i, 36))


Next i
 
End Sub

I'm using excel 2016.

Thanks

 

[offthelip]

Have a look at this thread which is about how to get rid of vlookup in VBA. The technique shoujld make your macor run about 1000 times quicker
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

 

[Eric4545]

I'll look into that thread. Many thanks.