How to speed up index match function

F

fluffyvampirekitten

Board Regular
Joined
Jul 1, 2015
Messages
72
I have this line of codes
however , my excel will lag and most of the time it will stop responding .
Why? :(
Cross Ref: How to speed up this index match function

Any suggestion ?

Code: 
Private Sub Unsuccessful()
'Update Column S and T
'S = Active Ext ID , T = Inactive Ext ID
Dim MaxRowNum As Long
Sheets("SimPat").Select
'Set up an Error handler
On Error GoTo errorFound
Err.Clear
On Error GoTo 0
    
'Vlookup/IndexMatch Active Ext ID
Range("S2").FormulaR1C1 = _
      "=INDEX('[PatientMerge.xls]2015'!C10,MATCH(C[-16],'[PatientMerge.xls]2015'!C10,0))"
'Vlookup/IndexMatch Inactive Ext ID
Range("T2").FormulaR1C1 = _
      "=INDEX('[PatientMerge.xls]2015'!C11,MATCH(C[-17],'[PatientMerge.xls]2015'!C11,0))"
    
    
    'Locate last filled row in column S (this instead of the loop)
    MaxRowNum = Range("S" & Rows.Count).End(xlUp).Row
 
    
    'Autofill the rest of the rows
    Range("S2:T2").Select
    Selection.AutoFill Destination:=Range("S2:T2" & MaxRowNum), Type:=xlFillDefault
    'Column S and T Autofit
    Columns("S:T").Select
    Columns("S:T").EntireColumn.AutoFit
            
    'Copy and Paste data as value
    Sheets("SimPat").Select 'Activate/Open Simpat again
    Range("S2:T2" & MaxRowNum).Select
    Selection.Copy
    Worksheets("Simpat").Range("U2:V2" & MaxRowNum).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
       
    Columns("S:S").Select
    Selection.Delete Shift:=xlToLeft
    Columns("T:T").Select
    Selection.Delete Shift:=xlToLeft
       
    Application.CutCopyMode = False
    
With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
End With

'Close the error Handler
Exit Sub
errorFound:
If Err.Number > 0 Then MsgBox Err.Description, vbCritical, "Error#: & Err.Number"
Err.Clear
End Sub
 
Sort by date Sort by votes
Yes....
Code: 
Private Sub Unsuccessful3()
'Update Column S and T
'S = Active Ext ID , T = Inactive Ext ID
Dim MaxRowNum As Long
With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
End With
Sheets("SimPat").Select
'Set up an Error handler
On Error GoTo errorFound
Err.Clear
On Error GoTo 0
    'Locate last filled row in column S (this instead of the loop)
    MaxRowNum = Range("C" & Rows.Count).End(xlUp).Row
'Vlookup/IndexMatch Active Ext ID
Range("S2:S" & MaxRowNum).Formula = "INDEX('[PatientMerge.xls]2015'!$J:$J,MATCH(C:C,'[PatientMerge.xls]2015'!$J:$J,0))"
'Vlookup/IndexMatch Inactive Ext ID
Range("T2:T" & MaxRowNum).Formula = "=INDEX('[PatientMerge.xls]2015'!$K:$K,MATCH(C:C,'[PatientMerge.xls]2015'!$K:$K,0))"
With Range("S2:T" & MaxRowNum)
        .Value = .Value
End With
    Columns("S:T").EntireColumn.AutoFit
With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
End With
'Close the error Handler
Exit Sub
errorFound:
If Err.Number > 0 Then MsgBox Err.Description, vbCritical, "Error#: & Err.Number"
Err.Clear
End Sub

My Patient File have thousands of records
Click to expand...
But not 1 million.....by reducing the ranges to say 10,000 or 20,000, you will speed up the calculations considerably
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Michael M said:
Yes....
Code: 
Private Sub Unsuccessful3()
'Update Column S and T
'S = Active Ext ID , T = Inactive Ext ID
Dim MaxRowNum As Long
With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
End With
Sheets("SimPat").Select
'Set up an Error handler
On Error GoTo errorFound
Err.Clear
On Error GoTo 0
    'Locate last filled row in column S (this instead of the loop)
    MaxRowNum = Range("C" & Rows.Count).End(xlUp).Row
'Vlookup/IndexMatch Active Ext ID
Range("S2:S" & MaxRowNum).Formula = "INDEX('[PatientMerge.xls]2015'!$J:$J,MATCH(C:C,'[PatientMerge.xls]2015'!$J:$J,0))"
'Vlookup/IndexMatch Inactive Ext ID
Range("T2:T" & MaxRowNum).Formula = "=INDEX('[PatientMerge.xls]2015'!$K:$K,MATCH(C:C,'[PatientMerge.xls]2015'!$K:$K,0))"
With Range("S2:T" & MaxRowNum)
        .Value = .Value
End With
    Columns("S:T").EntireColumn.AutoFit
With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
End With
'Close the error Handler
Exit Sub
errorFound:
If Err.Number > 0 Then MsgBox Err.Description, vbCritical, "Error#: & Err.Number"
Err.Clear
End Sub


But not 1 million.....by reducing the ranges to say 10,000 or 20,000, you will speed up the calculations considerably
Click to expand...


Okay , let's say if i reduce the range to around 5000 or 10000 , is it possible to create an error box , if i didnt satisfy the maxrownum of "SimPat" ?
 
Upvote 0
Why not simply make it large enough ???
If you used 100,000 it would still be 90% less than what you have now !!!
 
Upvote 0
Can I take a step back and ask what you want to match?

The reason I ask is because your code puts this formula in cell S2...
=INDEX('[PatientMerge.xls]2015'!$J:$J,MATCH(C:C,'[PatientMerge.xls]2015'!$J:$J,0))

Do you want to change C:C to C2 so as to lookup the value in from cell C2?

Why return the Indexed value from column J; the same column the match is searched within? And if it's a match, it's the same value in C2?
 
Upvote 0
AlphaFrog said:
Can I take a step back and ask what you want to match?

The reason I ask is because your code puts this formula in cell S2...
=INDEX('[PatientMerge.xls]2015'!$J:$J,MATCH(C:C,'[PatientMerge.xls]2015'!$J:$J,0))

Do you want to change C:C to C2 so as to lookup the value in from cell C2?

Why return the Indexed value from column J; the same column the match is searched within? And if it's a match, it's the same value in C2?
Click to expand...

Column J is in another workbook , yes its the same as C2( but C2 is at Simpat file) .
The another workbook is the main workbook( hold more information )
Okay ill make changes ^^
 
Upvote 0
Even if you change C:C to C2, I still don't understand the purpose of the lookup. Do you just want to know if C2 exists in the other workbook?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Help please! Can't figure out how to automatically generate reports and include task description in the generated Excel word doc
Replies
1
Views
242
ttp921
T
I
  • Question
Is there an easy way to add formulaR1C1 into VBA arrays to speed up calculation time?
Replies
4
Views
872
ianawwalker
I
A
  • Solved
Speed up macro of index match formulas running
2
Replies
15
Views
867
Apple08
A
J
  • Question
How to speed up the for - next statement using always the last 200 rows that contains text of a column
2
Replies
11
Views
891
Micron
M
A
  • Solved
Iferror index and match formula in VBA
2
Replies
12
Views
957
Fluff
Fluff

Forum statistics

Threads
1,215,013
Messages
6,122,690
Members
449,092
Latest member
snoom82

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top