VBA Index Match formula not working

[Jdw5511]

Hello,

I am attempting to write a macro and the Index match formula is not computing correctly as I am returning a #N/A. Here is my formula as it appears in VBA

Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Range("H2:H" & LR).Formula = "=INDEX(KPI_Metrics!E2:E" & LR & ", MATCH('Completed Allocated'! B2, KPI_Metrics!B2:B" & LR & ",0))"

Any assistance provided would be greatly appreciated!

 

[Marcelo Branco]

See if this works

Dim LR As Long
With Sheets("KPI_Metrics")
   LR = [B][COLOR=#ff0000].[/COLOR][/B]Range("A" & .Rows.Count).End(xlUp).Row
End with

With ActiveSheet
   Range("H2:H" & LR).Formula = "=INDEX(KPI_Metrics!E2:E" & LR & ", MATCH('Completed Allocated'! B2, KPI_Metrics!B2:B" & LR & ",0))"
End With

M.