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

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...ent-alternatives-vba-vlookup-index-match.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.