Macro enregistrée non ré-utilisable

Version imprimable

Bonjour a tous,
Devant reproduire en VBA une action sur plusieurs cellules, j'ai effectué par enregistrement de macros (sub macro3 ci dessous) le déroulé de l'action souhaitée qui fonctionne parfaitement...
Par contre quand je la relance, elle plante avec une erreur sur la formule saisie : run time error 1004 application defined or object defined error...
Pour info il s'agit d'une validation de cellule avec formule intégré (qui fonctionne parfaitement en direct!)
De memoire j'ai déja eu des pbs avec les formules, car j'utilise une version américaine de Excel 2003, avec pack français, ce qui fait que j'utilise d'habitude FormulaLocal et non Formula seul, mais je ne sais pas comment proceder dans ce cas de validation.
Un grand merci pour votre aide !
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Sub Macro3() Range("G5").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:= _ "=SI(D5<>"""";DECALER(Liste_SOCIETES;EQUIV(D3&""*"";Liste_SOCIETES;0)-1;;SOMMEPROD((STXT(Liste_SOCIETES;1;NBCAR(D5))=TEXTE(D5;""0""))*1));Liste_SOCIETES)" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = False End With End Sub
Pour info : cette formule dans un cas de validation permet l'affichage que de la partie d'une liste longue correspondante a la saisie du début du champ, très pratique !

03/07/2012, 18h26
Daniel.C

Bonjour,

Il faut traduire la formule en anglais :

Code:

=IF(D5<>"""",OFFSET(Liste_SOCIETES,MATCH(D3&""*"",Liste_SOCIETES,0)-1,,SUMPRODUCT((MID(Liste_SOCIETES,1,LEN(D5))=TEXT(D5,""0""))*1)),Liste_SOCIETES)

sauf erreur.
04/07/2012, 10h00
K-limero

:cry:Bien vu mais déjà essayé et bien sur cela ne fonctionne pas...
je pense qu'il faut arriver a prendre l'equivalent de FormulaLocal au lieu de Formula1, mais comment??
Pour info la ligne de commande en français fonctionne si par exemple j'ecris :
cells(1,1).formulaLocal = "=SI(D5<>"""";DECALER(Liste_SOCIETES;EQUIV(D3&""*"";Liste_SOCIETES;0)-1;;SOMMEPROD((STXT(Liste_SOCIETES;1;NBCAR(D5))=TEXTE(D5;""0""))*1));Liste_SOCIETES)"
cela ne fait pas d'erreur dans la cellule, mais quand j'utilise "Selection.Validation.add", cela n'accepte pas ce FormulaLocal...
Merci de votre aide
04/07/2012, 11h00
Daniel.C

J'ai essayé avec une formule plus simple. Ca fonctionne. On ne peut peut pas utiliser de "formulalocal". Je ne sais pas pourquoi la formule transposée ne fonctionne pas. Est-ce que tu pourrais mettre un classeur exemple - sans données confidentielles - en pièce jointe ?
04/07/2012, 11h18
K-limero

1 pièce(s) jointe(s)

Merci de ton aide Daniel.
Voila le fichier joint. J'ai créé la macro en enregistrant la mise en validation de la cellule C6.
Je dois par VBA, remettre la validation, une fois la cellule utilisée en service si les utilisateurs modifie la validation, (que je ne peux bloquer pour raison internes!), mais lorsque je relance la macro c'est la le probléme...
Encore merci !

Je ne sais pas trop où j'avais fait une erreur. Voici la macro :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Macro2()
    Range("C6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=IF(C6<>"""",OFFSET(Liste_SOCIETES,MATCH(C6&""*"",Liste_SOCIETES,0)-1,,SUMPRODUCT((MID(Liste_SOCIETES,1,LEN(C6))=TEXT(C6,""0""))*1)),Liste_SOCIETES)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
End Sub

04/07/2012, 12h35
K-limero

Merci a toi, tu m'as sauvé!!!:ccool::ccool:
Mais bon je ne comprends pas non plus car j'avais copié/collé tes données...
Enfin l'important c'est le résultat.

Pour info, j'ai une autre cellule qui au lieu d'etre des noms de société sont des numéros de badges, et là vu qu'il s'agit de Nombre, cette formule ne marche pas.
J'ai contourné le problème en transformant les nombres en Texte dans la liste initiale, mais cela ne me plais pas et de plus je ne peux les formater comme je veux, as tu une idée?
Encore merci !
04/07/2012, 15h17
Daniel.C

A vrai dire, je n'ai pas compris ce que tu voulais faire avec ta formule. Est-ce que tu peux expliquer ta problématique ?
05/07/2012, 09h39
K-limero

Oui bien sur,
Voila, a l'accueil de ma société, les agents en charge de fournir un badge visiteur, doivent saisir
Le nom/prénom de la personne, sa société, la personne visitée, le but de la visite. Elle affecte un badge pour la durée de la visite.
Aujourd'hui sur Excel elles saisissent chaque fois sur une ligne toutes ces données. Comme elles sont très peu formées a Excel, elles ressaisissent tout a chaque fois et donc les noms des personnes, les sociétés ne sont pas saisies a l'identique.
Je veux donc leur faire un onglet de saisie, qui leur propose les données deja saisies et de ce fait, la formule VALIDATION que je veux automatiquement mettre en œuvre pour chaque champ. Voila.
Tout est OK maintenant pour cet onglet, grace a toi(!), mais il ne me reste plus (je suis perfectionniste!) qu'à traiter en numérique, le champs "N° de badges", traité actuellement comme Texte...
Je peux, si cela intéresse quelqu'un lui transmettre mon fichier terminé(sans données bien sur...). Encore merci !:ccool:
05/07/2012, 10h03
Daniel.C

Oui, s'il te plait mets un classeur en PJ.
05/07/2012, 10h09
K-limero

OK je le finalise, enlève des données et le joint en fin d’après midi...