Concaténation références de cellules et point virgule

Version imprimable

20/10/2010, 11h49
michaeljeru

Concaténation références de cellules et point virgule
"Bonjour"

J'ai une macro qui compare 2 feuilles et affiche la différence dans une 3è feuille.
Ce que je voudrais, c'est écrire une formule dans la première case vide en fin de ligne, où toutes les colonnes seraient concaténer de la forme :
=CONCATENER(A1 ; ";" ; A2 ; ";" ; A3 ; ";" ...)

Or, dans le code VB de la macro,
Code:

1 2 3 4 Range(lastCol & k).Select //je sélectionne ma dernière colonne Syntaxe = ... ActiveCell.Value = Syntaxe
si j'écris

Citation:

Syntaxe = "A1"

la référence fonctionne correctement

si j'écris

Citation:

Syntaxe = "=CONCATENER(A1;A2)"

ou

Citation:

Syntaxe = "=CONCATENER(A1" & Chr(59) & A2 & ")"

j'ai inlassablement le même message d'erreur sur la ligne ActiveCell.value :

Citation:

Erreur d'exécution 1004 : Erreur définie par l'application ou par l'objet

Je sens bien que c'est le point virgule qui pose problème. or il faut que je résolve ce problème, car dans la formule finale, j'ai plus de 50 champs concaténés de la sorte...

merci pour votre aide

Bonjour,
formules dans feuille feuil2 de la colonne 1 à la colonne 10

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Dim i As Long, LastLig As Long
Dim Frml As String
 
With Sheets("Feuil2")
    LastLig = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To LastLig
        Frml = Frml & ";A" & i
    Next i
    Frml = Mid(Frml, 2)
    .Cells(LastLig + 1, 1).FormulaLocal = "=CONCATENER(" & Frml & ")"
    .Cells(LastLig + 1, 1).AutoFill .Range(.Cells(LastLig + 1, 1), .Cells(LastLig + 1, 10))
End With

20/10/2010, 14h12
michaeljeru

j'avais effectivement trouver une façon de le faire en utilisant le FomulaLocal. Par contre, je suis obligé de saisir toutes mes colonnes (47) à la main, car le nombre de paramètres acceptés par la fonction Concaténer est limité à 30 !

Excel 2007 permet 255 paramètres.
sinon tu peux concaténer intrinsèquement les valeurs

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Dim i As Long, LastLig As Long
Dim j As Byte
Dim Frml As String
 
With Sheets("Feuil2")
    LastLig = .Cells(Rows.Count, 1).End(xlUp).Row
    For j = 1 To 47                             'De la colonne 1 à la colonne 47
        For i = 1 To LastLig
            Frml = Frml & .Cells(i, j).Value
        Next i
        .Cells(LastLig + 1, j).Value = Frml
        Frml = vbNullString
    Next j
End With

J'ai essayé avec ta méthode, remis à ma sauce, mais j'ai une erreur :

Code:

1
2
3
4
5
6
7
8
 
 For j = 1 To LastLig2                         
                For i = 1 To LastCol            'De la colonne 1 à la colonne 47
                    Frml = Frml & sw2.Cells(j, i).Value
                Next i
                sw3.Cells(LastCol + 1, j).Value = Frml
                Frml = vbNullString
             Next j

Citation:

Erreur d'exécution 424 : Objet requis

sur la ligne Frml = Frml & sw2.Cells(j, i).Value

20/10/2010, 15h29
mercatog

Et c'est quoi ta sw2 et ta sw3?

coquille de ma part, c'était ws2 et ws3, pour worksheet(2) et worksheet(3).

j'ai donc continué, mais je me frotte à un pb que j'essaye sans succès de contourner : il semble y avoir un problème quand j'insère une chaine contenant un = ... mais dans certains cas seulement...

Si je fais :
Code:

1 2 Var = "=concatener(A1;" & """;""" & ";" & "A2;" & """;""" & ";" & "A3;" & """;""" & ")" ws3.Cells(nbLignesFinal, LastCol + 1).FormulaLocal = Var
j'obtiens bien le résultat escompté, à savoir la chaîne : "NOM;PRENOM;DATENAISSANCE", sachant que les colonnes A1 à A3 correspondent respectivement au Nom, Prenom et Date de naissance des clients.

Par contre, si je prends la formule générique complète (sur les 47 colonnes) :
Code:

1 2 3 4 5 6 For a = 1 To LastCol tmp = ws2.Cells(i, a).Value Formule = Formule & tmp & ";" Next a ws3.Cells(nbLignesFinal, LastCol + 1).FormulaLocal = "Concatener(" & Formule & ")" Formule = vbNullString
ça passe avec FormulaLocal = "Concatener(...", (j'obtiens donc la chaîne concatener(avec tous les arguments et points virgules qui vont bien) mais la formule n'est pas calculer puisqu'il manque le =) mais ça ne passe pas avec FormulaLocal = "=Concatener(..."

ce qui me donne l'erreur 1004 : Erreur définie par l'application ou l'objet

alors je me suis dit que le pb venait que j'entrais directement dans la cellule le symbole =, donc j'ai essayé d'initialiser Formule à "=Concatener", mais cela ne change rien au problème...

Je suis désolé de te solliciter autaut, et j'en profite pour te remercier de ton aide précieuse.

20/10/2010, 18h00
tototiti2008

Bonjour à tous,

En XL2003 et inférieur, Concatener, comme toutes les fonctions Excel est limité à 30 arguments séparés par des ";"
Pour 2007, je ne sais pas à combien est la limite

Est-ce que la formule construite utilise plus de 30 arguments ?
Est-ce que tu travailles sur XL2003 ou inférieur ?

Oui, je suis sous 2003 malheureusement.
Et oui, la formule finale comporte plus de 30 arguments.
Du coup, j'ai essayé de parer à la situation en remplaçant la séquence

Code:

1
2
3
4
5
 
'boucle de concaténation des colonnes -> Formule
Range(NomLastCol & k).Select
Syntaxe = "=CONCATENER(" & Formule & ")"
ActiveCell.FormulaLocal = Syntaxe

par

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
Range(NomLastCol2 & k).Select   'case de la colonne 49
 
Syntaxe1 = "Concatener( A" & k & ";" & """;""" & ";B" & k & ";" & """;""" & ";C" & k & ";" & """;""" & ";D" & k & ";" & """;""" & ";C" & k & ";" & """;""" & ";C" & k & ";)"
Syntaxe2 = "Concatener(" & Syntaxe1 & ";" & """;""" & ";F" & k & ";" & """;""" & ";G" & k & ";" & """;""" & ";H" & k & ";" & """;""" & ";I" & k & ";" & """;""" & ";J" & k & ";)"
Syntaxe3 = "Concatener(" & Syntaxe2 & ";" & """;""" & ";K" & k & ";" & """;""" & ";L" & k & ";" & """;""" & ";M" & k & ";" & """;""" & ";M" & k & ";" & """;""" & ";O" & k & ";)"
Syntaxe4 = "Concatener(" & Syntaxe3 & ";" & """;""" & ";P" & k & ";" & """;""" & ";P" & k & ";" & """;""" & ";R" & k & ";" & """;""" & ";S" & k & ";" & """;""" & ";T" & k & ";)"
Syntaxe5 = "Concatener(" & Syntaxe4 & ";" & """;""" & ";U" & k & ";" & """;""" & ";V" & k & ";" & """;""" & ";W" & k & ";" & """;""" & ";X" & k & ";" & """;""" & ";Y" & k & ";)"
Syntaxe6 = "Concatener(" & Syntaxe5 & ";" & """;""" & ";Z" & k & ";" & """;""" & ";AA" & k & ";" & """;""" & ";AB" & k & ";" & """;""" & ";AC" & k & ";" & """;""" & ";AD" & k & ";)"
Syntaxe7 = "Concatener(" & Syntaxe6 & ";" & """;""" & ";AE" & k & ";" & """;""" & ";AF" & k & ";" & """;""" & ";AG" & k & ";" & """;""" & ";AH" & k & ";" & """;""" & ";AI" & k & ";)"
 
ActiveCell.FormulaLocal = "=" & Syntaxe7
 
SyntaxePrevious = ActiveCell.Value
Syntaxe8 = "Concatener(" & SyntaxePrevious & ";" & """;""" & ";AJ" & k & ";" & """;""" & ";AK" & k & ";" & """;""" & ";AL" & k & ";" & """;""" & ";AM" & k & ";" & """;""" & ";AN" & k & ";)"
Syntaxe9 = "Concatener(" & Syntaxe8 & ";" & """;""" & ";AO" & k & ";" & """;""" & ";AP" & k & ";" & """;""" & ";AQ" & k & ";" & """;""" & ";AR" & k & ";" & """;""" & ";AS" & k & ";)"
Syntaxe10 = "Concatener(" & Syntaxe9 & ";" & """;""" & ";AT" & k & ";)"
 
Range(NomLastCol & k).Select
ActiveCell.FormulaLocal = "=" & Syntaxe7             'ligne où sort l'erreur
 
Range(NomLastCol2 & k).Select
ActiveCell.FormulaLocal = ""             'vidage de la cellule temporaire

Le code ci dessus fonctionne et donne parfaitement ce que je veux, mais uniquement jusqu'à la concaténation de Syntaxe7.Si j'essaye de mettre Syntaxe8, 9 ou 10, j'obtiens la même erreur 1004 que précédemment sur la ligne du FormulaLocal.
Pourtant, je contourne la limitation du concaténer, en ne mettant que 10 arguments par appel de concaténer, mais là, j'ai l'impression que c'est une limitation sur la longueur de la string. Or d'après ce que j'ai trouvé sur le site MSDN, la limite de taille d'une string est de 2^31 caractères...

21/10/2010, 10h49
mercatog

Tu es obligé de passer par formule?
21/10/2010, 10h51
michaeljeru

Non, je peux très bien concaténer directement les valeurs, mais comment faire avec ce problème de limite de concaténation ?

Je pense que tu dois adapter ce que j'avais proposé ci-haut
ci-joint code qui, dans feuil2, transcrit la concaténation des valeurs de chaque colonne en fin

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Public Sub aaa()
Dim i As Long, LastLig As Long
Dim LastCol As Integer
Dim j As Byte
Dim CONCA As String
 
With Sheets("Feuil2")
    LastLig = .Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    For j = 1 To LastCol                                'De la colonne 1 à la colonne LastCol
        For i = 1 To LastLig                            'On boucle sur le contenu de chaque colonne j et on concatène
            CONCA = CONCA & .Cells(i, j).Value & ";"
        Next i
        .Cells(LastLig + 1, j).Value = CONCA             'On écrit la valeur après la dernière cellule remplie
        CONCA = ""                                       'On initalise CONCA
    Next j
End With
 
End Sub

Edit: Sans formule, j'ai testé sur 20000 lignes

21/10/2010, 11h22
michaeljeru

Ca fonctionne !!
J'ai compris, c'est effectivement le CONCA = CONCA & ws2.Cells(i, j).Value & ";" qui me permet de copier la valeur de la cellule.
mais par contre, je ne comprends quand même pas ce qui ne marchait pas avec le passage par formule. Il a semblé y avoir une saturation, que je ne pige pas.

En tout cas, merci grandement pour ton aide, tu m'as bien aidé, et débutant que je suis, je n'aurait pas réussi aussi vite à rentrer dans le VB sans ton aide.
21/10/2010, 11h45
mercatog

L'aide d'Excel 2007 sur la fonction:

Citation:

CONCATENER (texte1;texte2;...)

texte1;texte2; ... représentent les 2 à 255 éléments de texte à assembler en un seul élément de texte. Les éléments de texte peuvent être des chaînes de caractères, des nombres ou des références à des cellules uniques