Match function doesn't work in VBA

[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

 

[Don Guillett]

When you use functions in vba you must use the range
try

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

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

 

[Dana DeLouis]

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.

 

[Lucien.Lineatte]

This solution works finely.
Thank You
Lucien

Dana DeLouis a écrit :