I have a worksheet that contains values, and I need to match one of the values to a lookup worksheet. This syntax works like it should, however, it takes roughly 10 minutes to fully execute. This could be due to the size of the worksheets: Sheet1 has roughly 18,000 rows and Sheet2 has roughly 20,000 rows.  Is there any optimization that can take place in this syntax?Function TwoMatches()Dim wSheet As Worksheet: Set wSheet = Sheets(Sheet2)Dim lookupSheet As Worksheet: Set lookupSheet = Sheets(Sheet1)Dim lr As Long: lr = wSheet.Cells(wSheet.Rows.Count, A).End(xlUp).RowDim destSheet As Worksheet: Set destSheet = Sheets(Sheet2)Set lookupRange = Sheets(Sheet1).Range($A:$C)Dim Found As Range'Add a column at the beginning to Trim() and Remove HyphensColumns(A:A).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove'Add Clean Sheet1 Name As Header TextActiveWorkbook.Sheets(Sheet2).Range(A1).Value = Clean Sheet1 Name'This is the worksheet formula that I want to use for the used rangeWith wSheet    lr = .Cells(.Rows.Count, D).End(xlUp).Row    form = =TRIM(SUBSTITUTE(D2,-, ))    .Range(A2:A & lr).Formula = formEnd With'Get the Last Column With DataActiveWorkbook.Sheets(Sheet2).Range(P1).Value = LookupValFor i = Range(A & Rows.Count).End(3).Row To 2 Step -1    'Pulling value where column O is not null    If Not IsEmpty(Cells(i, O).Value) Then    With Sheets(Sheet1)        'Filter criteria one is where column B IS NOT NULL        .Range($A:$C).AutoFilter Field:=2, Criteria1:=<>        Set Found = .Range(A:A).Find(What:=wSheet.Cells(i, A).Value, _                               LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _                               MatchCase:=False)        If Not Found Is Nothing Then            wSheet.Cells(i, P).Value = Found.Offset(, 2).Value        End If    End With    'Pulling value where column O is null    Else    With Sheets(Sheet1)        'Filter criteria one is where column B IS NULL        .Range($A:$C).AutoFilter Field:=2, Criteria1:==        Set Found = .Range(A:A).Find(What:=wSheet.Cells(i, A).Value, _                               LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _                               MatchCase:=False)        If Not Found Is Nothing Then            wSheet.Cells(i, P).Value = Found.Offset(, 2).Value        End If    End With    End IfNext i'Removing the column added for the Clean MatchRange(A:A).DeleteEnd Function
Matching values to a lookup worksheet
performance;vba;excel