Formula in VBA

[habiler]

Good morning,


I would like to put the following formula in VBA



=INDEX(grades[#All];MATCH(B2;grades_FullDen!M:M);5)
grades[#All] = Table with all records in the grades_FullDen sheet

As this didn’t work I dissected the formula starting with:



=MATCH(B2;grades_FullDen!M:M)

Là il me donne une erreur Mismatch




"= Application.Match(Sh1, Sh2)"

Quelqu'un poorrait-il m'aider ?

Un grand merci d'avance

Habiler





Private Sub Worksheet_Change(ByVal Target As Range)
Set SH1 = Sheets("GrdVsFct").Range("B2")
'Set SH2 = Sheets("Grades_FullDen").Range("full_grade_f")
Set SH2 = Sheets("Grades_FullDen").Range("M1:M300")

If Target.Address = "$B$2" Then
'[B5:s9].ClearContents
Debug.Print SH1
Debug.Print SH2
Sheets("Grades_FullDen").[M1].CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=[B1:B2], CopyToRange:=[B4:J4]
Menu Target
Sheets("GrdVsFct").Range("A2").Formula = "= Application.Match(Sh1, Sh2)"
End If EndSub

[mancubus]

=Match(Sh1, Sh2)

if you omit the third argument in MATCH formula the default value is 1.





Match_type
Behavior


1 or omitted
MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

[mancubus]

for index match; probably...


Range("A2").Formula = "=INDEX(TableNameHere[#All],MATCH(Sh1,TableNameHere[[#All],[ColumnHeaderHere]],0),5)"


ColumnHeaderHere indicates the lookup array in the match function. just replace it with the real header in your table.