Match function doesn't work in VBA

  • Thread starter Thread starter Lucien.Lineatte
  • Start date Start date
L

Lucien.Lineatte

cnLigne is a named group of cells in the same row
(Ville Janvier Février Mars Avril Mai Juin)
EQUIV stands for MATCH in french

When I put in a cell ( C29 in my test)
= EQUIV("janvier"; cnLigne;0)
the result is 2 and it is correct.

When I put in a VBA macro
Cells(29, "C").FormulaR1C1 = Match("janvier", cnLigne, 0)
the result is an error
erreur de compilation
Sub ou Fonction non définie (Sub or function not defined)

I have the same kind of problem with INDEX which has the same name in
french and in english.

Can anyone help me ?
Regards.
Lucien
 
When you use functions in vba you must use the range
try
Cells(29, "C").FormulaR1C1 = Match("janvier", cnLigne, 0)
Click to expand...

Cells(29, "C").Formula = "=Match("janvier",range(cnLigne), 0)"
and to change the formula to a value after it is calculated, use
Cells(29, "C").value =Cells(29, "C").value
 
Cells(29, "C").FormulaR1C1 = Match("janvier", cnLigne, 0)


Try putting quotes around your function. Since "janvier" is also in quotes,
use double quotes around "janvier." Don't forget the equal sign '=' also.
:>)

Cells(29, "C").Formula = "=Match(""janvier"", cnLigne, 0)"

HTH.
 
Back
Top