VBA INDEX and MATCH with 2 different tables

[_Chanty_]

Hi,

I have 2 tables named FDT_Previous and FDT_1. I want to retreive the hours in column FDT_1 [8/18/24] the hours from column FDT_Previous [8/18/24] where "employee number" "task code" are the same.

The formula in my column FDT_1 [8/18/24] is working perfectly :
=IFERROR(INDEX(FDT_Previous[8/18/24], MATCH(1, (FDT_Previous[employee number] = [@[employee number]]) * (FDT_Previous[task code] = [@[task code]]), 0)), "")

When I'm doing this code in the VBA :

ActiveCell.FormulaR1C1 = "=IFERROR(INDEX(FDT_previous[" & Prev_dimanche & "], MATCH(1, (FDT_previous[employee number] = [@[employee number]]) * (FDT_previous[task code] = [@[task code]]), 0)), """")"

The result is :
=IFERROR(INDEX(FDT_Previous[8/18/24], MATCH(1, (FDT_Previous[@[employee number]] = [@[employee number]]) * (FDT_Previous[@[task code]] = [@[task code]]), 0)), "")

That's mean the formula is incorrect. It change my reference column into cell reference and it's not appropriate.

I tried multiple approaches with the same failed result.

How can I resolve this ?

Thanks

 

[DanteAmor]

Hi and welcome to MrExcel !

Try:

Sub Macro_Index_Match()
  With Range("FDT_1[18/08/2024]")
    .FormulaR1C1 = _
        "=INDEX(FDT_Previous[[13/08/2024]:[18/08/2024]],MATCH([@Emp],FDT_Previous[Emp],0),MATCH(FDT_1[[#Headers],[18/08/2024]],FDT_Previous[[#Headers],[13/08/2024]:[18/08/2024]],0))"
  End With
End Sub

If you want only the values:

Sub Macro_Index_Match()
  With Range("FDT_1[18/08/2024]")
    .FormulaR1C1 = _
        "=INDEX(FDT_Previous[[13/08/2024]:[18/08/2024]],MATCH([@Emp],FDT_Previous[Emp],0),MATCH(FDT_1[[#Headers],[18/08/2024]],FDT_Previous[[#Headers],[13/08/2024]:[18/08/2024]],0))"
    .Value = .Value
  End With
End Sub

 

[_Chanty_]

Hi and thanks for your welcome,

This code works after I adjust the header names :

Sub Macro_Index_Match()
With Range("FDT_1[8/18/24]")
.FormulaR1C1 = _
"=INDEX(FDT_Previous[[task code]:[8/18/24]],MATCH([@employee number],FDT_Previous[employee number],0), " & _
"MATCH(FDT_1[[#Headers],[8/18/24]],FDT_Previous[[#Headers],[task code]:[8/18/24]],0))"
End With
End Sub


Therefore, this formula miss the part where I need to match the task code as well.
(FDT_previous[task code] = [@[task code]])

Can you adjust it ?

Thanks