habiler
04-19-2023, 02:06 AM
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
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