VBA Help - Code that does a lookup based on Cell and brings in metadata - Excel 2016

[Johnny Thunder]

Hello All,


I am working on a project and have this code that works but its super slow, takes 15-25 seconds to bring the data into my sheet?

Context, this is a report for an HR Department. A user can drop in a tracking number and from a Data Mart sheet the code looks up the details stored and brings them into a User Friendly Sheet for the user to see what attributes have already been filled in and the user can then make edits and with another code (works great) the newly edited fields will override the old detail.

I am having trouble with speed to just bring in the details with the code below. This code is triggered by a Worksheet Event and the event already has Screen Updating turned Off/On at the start as well as Calculations, didn't do much though. The code runs super slow even when triggered manually. Any ideas are greatly appreciated. Thanks

Sub PopulateEntry()


Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastR As Long
Dim User As String, Selection As Range, Tracking As String
Dim cell As Range, CurrPostion As Range


Set ws1 = Sheets("Start")
Set ws2 = Sheets("Data Mart")


LastR = ws2.Range("B" & Rows.Count).End(xlUp).Row 'Finds last row with data


Tracking = ws1.Range("C16").Value


Set Selection = ws2.Cells.Find(What:=Tracking, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)


        With ws1
            
            .Range("C19").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),1)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),1)),"""")"   'First Name
                    .Range("F19").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),2)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),2)),"""")"   'Last Name
                        .Range("F28").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),4)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),4)),"""")"  'Division
                        .Range("C22").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),4)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),5)),"""")"  'Department
                            .Range("F22").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),5)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),6)),"""")"   'Department Transfer
                                .Range("I22").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),6)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),7)),"""")"   'Prior Department
                                    .Range("C25").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),8)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),9)),"""")"   'OCOGS
                                        .Range("F25").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),9)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),10)),"""")"   'OPEX
                                    .Range("C28").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),10)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),12)),"""")"    'Requisition #
                                .Range("C31").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),12)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),13)),"""")"   'Start Date
                            .Range("F31").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),13)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),14)),"""")"     'End Date
                        .Range("C34").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),3)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),3)),"""")"     'Position
                    .Range("F34").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),7)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),8)),"""")"     'Level
                .Range("C37").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),14)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),15)),"""")"     'Annual Salary
            .Range("F37").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),15)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),16)),"""")"     'Sign On Cash
        .Range("I37").Formula = "=IFERROR(IF(INDEX('Data Mart'!$B$3:$S$179,MATCH($C$16,'Data Mart'!$L$3:$L$179,0),16)="""","""",INDEX('Data Mart'!$B$3:$S$179,MATCH(Start!$C$16,'Data Mart'!$L$3:$L$179,0),17)),"""")"     'Corp bonus
               
        
        .Range("C19").Value = .Range("C19").Value   'First Name
            .Range("F19").Value = .Range("F19").Value  'Last Name
                .Range("C22").Value = .Range("C22").Value   'Department
                .Range("F28").Value = .Range("F28").Value   'Division
                    .Range("F22").Value = Range("F22").Value   'Department Transfer
                        .Range("I22").Value = .Range("I22").Value   'Prior Department
                            .Range("C25").Value = .Range("C25").Value   'OCOGS
                                .Range("F25").Value = .Range("F25").Value  'OPEX
                                    .Range("C28").Value = .Range("C28").Value   'Requisition #
                                .Range("C31").Value = .Range("C31").Value  'Start Date
                            .Range("F31").Value = .Range("F31").Value  'End Date
                        .Range("C34").Value = .Range("C34").Value  'Position
                    .Range("F34").Value = .Range("F34").Value   'Level
                .Range("C37").Value = .Range("C37").Value  'Annual Salary
            .Range("F37").Value = .Range("F37").Value   'Sign On Cash
        .Range("I37").Value = .Range("I37").Value   'Corp bonus
        
        End With


Application.Goto ws1.Range("C16")


    Application.CutCopyMode = False
    Calculate


End Sub

 

[Johnny Thunder]

Bump