Amend Code to work in earlier versions

[howard]

I have Code below that works 100% in Office 2021, but not in Excel 2010

Kindly amend code, so that it will work on earlier versions as well

I know that Formula2R1C1 need to change to Formula1R1C1, but don't know how to replace Unique with a formula in Office 2010 that will generate same result

Kindly amend my code

 Sub Formula()
Dim LR As Long
With Sheets("Howard")
LR = .Cells(.Rows.Count, "L").End(xlUp).Row

.Range("E12:E" & LR + 14).ClearContents
.Range("e12").Formula2R1C1 = "=UNIQUE(R[-11]C[9]:R[2]C[9])"
.Range("e12:E" & LR + 14).Copy
.Range("E12:E" & LR + 14).PasteSpecial xlValues

End With
Application.CutCopyMode = False
End Sub

 

[MARK858]

Untested but try the code below

Sub FormulaEnter()
    Dim LR As Long
   
    With Sheets("Howard")
        LR = .Cells(.Rows.Count, "L").End(xlUp).Row

        With .Range("E12:E" & LR + 14)
            .ClearContents
            .FormulaR1C1 = "=IFERROR(INDEX(R1C14:R14C14, MATCH(0, INDEX(COUNTIF(R11C5:R[-1]C5, R1C14:R14C14), 0, 0), 0)), """")"
            .Value = .Value
        End With
   
    End With

End Sub

Btw, I personally wouldn't name a Sub the same as a word VBA uses

 

[howard]

Thanks Mark-Your code works 100%

 

[MARK858]

You're welcome