Vlookup and Index Match Formula Calculating Issue - running more than 20 Minutes for 3L lines

[Guna13]

Hi Sir,

I will update Vlookup or Index, match from another workbook to my master data (my master data has more than 3L lines), and record Formulas and put them in VBA code. To update the formula, it will take more than 30 minutes. How to proceed. There is no reason why VBA does not support this easy formula calculation.

All the way through, I tried. However, I do not know how making it as fast as possible. Excel 64-bit version

Sub Update_MDM_FDSS()

    With Application
    '    CalcMode = .Calculation
       .Calculation = xlCalculationManual
        .ScreenUpdating = False
         .EnableEvents = False
        .DisplayAlerts = False
    End With



cddr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(cddr) = Evaluate("IF(" & cddr & "="""","""",TRIM(" & cddr & "))")

addr = "B1:B" & Cells(Rows.Count, "B").End(xlUp).Row
Range(addr) = Evaluate("IF(" & addr & "="""","""",TRIM(" & addr & "))")

Range("M2").FormulaR1C1 = "=RC[-12]&RC[-11]"
Range("N1").Value = "Global Acc"
Range("O1").Value = "Global Desc"
Range("P1").Value = "Type"
Range("Q1").Value = "MEP_Code"
Range("Q2").FormulaR1C1 = "=RC[-16]&""_""&RC[-10]"

    Range("N2").FormulaR1C1 = "=VLOOKUP(RC[-1],'C:\ME Financial Report\ME_Financial_Project_File\[MDM_Con_File.xlsb]Sheet1'!C1:C5,5,0)"
    Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-2],'C:\ME Financial Report\ME_Financial_Project_File\[MDM_Con_File.xlsb]Sheet1'!C1:C6,6,0)"
   
'Range("N2").FormulaR1C1 = "=VLOOKUP(RC[-1],'C:\ME Financial Report\ME_Financial_Project_File\[MDM_Con_File.xlsb]Sheet1'!C1:C5,5,0)"
Range("P2").FormulaR1C1 = "=IF(LEFT(RC[-2],1)=""1"",""BS"",(IF(LEFT(RC[-2],1)=""2"",""BS"",""PL"")))"
Range("Q2").FormulaR1C1 = "=RC[-16]&""_""&RC[-10]"
'Range("R2").FormulaR1C1 = "=INDEX(FDSS_Busorg_Map.xlsx!C6,MATCH(RC[-1],FDSS_Busorg_Map.xlsx!C3))"

Range("R2").FormulaR1C1 = "=INDEX('C:\ME Financial Report\ME_Financial_Project_File\[FDSS_Busorg_Map.xlsx]FDSS_Busorg_Map'!C6,MATCH(RC[-1],'C:\ME Financial Report\ME_Financial_Project_File\[FDSS_Busorg_Map.xlsx]FDSS_Busorg_Map'!C3))"
 
   
'lastrow = ActiveSheet.Range("A65536").End(xlUp).Row
Range("M2:O2").AutoFill Destination:=Range("M2:O" & Range("A" & Rows.Count).End(xlUp).Row)
Range("P2:R2").AutoFill Destination:=Range("P2:R" & Range("A" & Rows.Count).End(xlUp).Row)
   
   
Application.CutCopyMode = False
lr = ActiveSheet.Range("A65536").End(xlUp).Row

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        '.Calculation = CalcMode
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
      End With

ActiveWorkbook.Save

Set rng2 = Range(Cells(1, 13), Cells(lr, 18))

rng2.Value2 = rng2.Value2
End Sub

 

[Anthony47]

You know which is the remote file your formulas need to refer; so you may Open that workbook when your macro begin and Close it just before End sub. Something like

Sub Update_MDM_FDSS()

    With Application
    '    CalcMode = .Calculation
       .Calculation = xlCalculationManual
        .ScreenUpdating = False
         .EnableEvents = False
        .DisplayAlerts = False
    End With
    
Dim remWb As Workbook                   '++
Set remWb = Workbooks.Open("C:\ME Financial Report\ME_Financial_Project_File\FDSS_Busorg_Map.xlsx") '++
ThisWorkbook.Activate                   '++
'
'your code
'your code
'
rng2.Value2 = rng2.Value2
remWb.Close False                       '++
End Sub

The added lines are marked "++"

Try...