I've used this Fuzzy Vlookup macro many times and it seems to work well; however, it seems to really eat up the resources on the PC when run. Well, at least on my dinosaur PC.
Any thoughts on how this can be made to run quicker? I'm trying to match the name starting in A2 with the list of names in column C. The lookup matches will go in column E with the true or false match in column F. Macro attached in workbook.
Just for testing purposes I've built in only 3 loops.
Increase macro speed for a Fuzzy Vlookup search
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Increase macro speed for a Fuzzy Vlookup search
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Increase macro speed for a Fuzzy Vlookup search
The FillTable macro took 25 seconds on my PC, so a bit over 8 seconds per run.
The code is too complicated to analyze, so I cannot suggest improvements.
You might try Microsoft's free Fuzzy Lookup Add-In. Since it is compiled, it should be faster. I haven't used it myself.
The code is too complicated to analyze, so I cannot suggest improvements.
You might try Microsoft's free Fuzzy Lookup Add-In. Since it is compiled, it should be faster. I haven't used it myself.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Increase macro speed for a Fuzzy Vlookup search
Hi Hans, yes, this code take about 30 seconds on my computer, but with 4000 rows, this would take more than 20 mins.
Unfortunately, I can't install any add-ins on my work computer, so that won't be an option.
The FillTable macro was just my attempt, but do you have a suggestion on something to replace it that would write the formula in column E in chunks versus all 4000 rows? I started with loops of 100 rows at a time, but this was just testing things out.
I also thought, maybe writing the 100 cells with the formula and then converting those cells to just values would help, but it doesn't appear to make a difference. I downloaded the recalc file from Charles Williams Decision Models and it doesn't appear the FuzzyVLookup is volatile.
I also added turning on/off the screen, but that also only appeared to save a second or so.
Unfortunately, I can't install any add-ins on my work computer, so that won't be an option.
The FillTable macro was just my attempt, but do you have a suggestion on something to replace it that would write the formula in column E in chunks versus all 4000 rows? I started with loops of 100 rows at a time, but this was just testing things out.
I also thought, maybe writing the 100 cells with the formula and then converting those cells to just values would help, but it doesn't appear to make a difference. I downloaded the recalc file from Charles Williams Decision Models and it doesn't appear the FuzzyVLookup is volatile.
I also added turning on/off the screen, but that also only appeared to save a second or so.
Code: Select all
Sub TurnEverythingOff()
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
End With
End Sub
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Increase macro speed for a Fuzzy Vlookup search
Fuzzy lookup is a process that by necessity requires a very large number of steps. I don't see ways to speed it up, sorry.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Increase macro speed for a Fuzzy Vlookup search
Okay, no worries.