Problème avec formulaR1C1

**Pierre67** · 31/07/2015, 18h12

Bonjour à tous

dans les cellules cells (a,12) de la colonne L d'un tableau, je vais extraire depuis la colonne C une chaine de caractère comprise entre les signes "-" pour toutes les lignes de mon tableau avec la macro suivante et cela fonctionne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
Sub occurence()
Dim a As Integer, i As Integer
a = 4
For i = 4 To 79
     Cells(a, 12).Select
     ActiveCell.FormulaR1C1 = "=MID(RC[-9],(SEARCH(""-"",RC[-9],1))+1,SEARCH(""-"",RC[-9],(SEARCH(""-"",RC[-9],1))+1)-((SEARCH(""-"",RC[-9],1))+1))"
     a = a + 1
Next i
End Sub

mon tableau comporte plusieurs colonnes.
je peux recopier la macro pour chaque colonne cells(a,13).select ..... et la formule reste identique mais pas top

j'aimerai pouvoir incrémenter une variable pour les colonnes et ainsi balayer tout mon tableau.

Mais je me heurte à la syntaxe dans RC

quelqu'un aurait-il une idée

merci à vous

**parmi** · 01/08/2015, 00h22

Bonjour,

J'ai de la difficulté à comprendre pourquoi les gens utilisent les formules R1C1 quand on peut utiliser Formula.
Les formules sont difficiles à lire par rapport à ce qu'on écrit dans les cellules de façon manuelle.

Quelle est cette formule que tu écris en colonne L et quelle serait celle que tu écrirais dans les autres ?

**Pierre67** · 01/08/2015, 09h35

Bonjour

'ai de la difficulté à comprendre pourquoi les gens utilisent les formules R1C1 quand on peut utiliser Formula.

C'est parce qu'on a utilisé l'enregistreur de macros, si mes connaissances en vba étaient plus fournies ... je ne poserai peut-être pas cette question

Quelle est cette formule que tu écris en colonne L et quelle serait celle que tu écrirais dans les autres ?

Cells(a, 12).Select définit la colonne L

La formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ActiveCell.FormulaR1C1 = "=MID(RC[-9],(SEARCH(""-"",RC[-9],1))+1,SEARCH(""-"",RC[-9],(SEARCH(""-"",RC[-9],1))+1)-((SEARCH(""-"",RC[-9],1))+1))"

grâce a cette formule je fais mais ma recherche dans la colonne C
dans la colonne M j'irai chercher les valeurs de la colonne D donc toujours le même décalage RC-9, donc même formule

**SfJ5Rpw8** · 01/08/2015, 11h27

bonjour,

pas vraiment clair il semble que tu contredise dans tes message : les formules dans les colonnes L et M ne sont-elles pas identiques ?

**Pierre67** · 01/08/2015, 12h00

bonjour

oui elles sont identiques
d'où ma question: au lieu d'écrire plusieurs lignes de code pour des formules identiques comment remplacer le RC[-9] par une variables sur les colonnes

**SfJ5Rpw8** · 01/08/2015, 12h03

Je comprends toujours pas ... si elle sont identique pourquoi veut tu une variable pour les modifier en fonction de la colonne destinataire ??

**joe.levrai** · 01/08/2015, 12h08

Bonjour,

regarde si tout ceci te permet de perfectionner l'utilisation des formules sous VBA
4 méthodes, 4 résultats identiques

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sub occurence()
Dim a As Integer, i As Integer
Dim Chaine As String
 
' colonnes L et M
For i = 12 To 13
    ' lignes 4 à 79
    For a = 4 To 79
 
        With Cells(a, i)
 
            ' adresse de la cellule située 9 colonnes avant
            Chaine = Replace(.Offset(0, -9).Address, "$", "")
 
            ' formula R1C1
            .FormulaR1C1 = "=MID(RC[-9],(SEARCH(""-"",RC[-9],1))+1,SEARCH(""-"",RC[-9],(SEARCH(""-"",RC[-9],1))+1)-((SEARCH(""-"",RC[-9],1))+1))"
 
            ' formula Local
            .FormulaLocal = "=STXT(" & Chaine & ";(CHERCHE(""-"";" & Chaine & ";1))+1;CHERCHE(""-"";" & Chaine & ";(CHERCHE(""-"";" & Chaine & ";1))+1)-((CHERCHE(""-"";" & Chaine & ";1))+1))"
 
            ' formula
            .Formula = "=MID(" & Chaine & ",(SEARCH(""-""," & Chaine & ",1))+1,SEARCH(""-""," & Chaine & ",(SEARCH(""-""," & Chaine & ",1))+1)-((SEARCH(""-""," & Chaine & ",1))+1))"
 
            ' calcul sous VBA et écriture du résultat
            ' sans écrire la formule dans excel
            If UBound(Split(.Offset(0, -9), "-")) > 0 Then .Value = Split(.Offset(0, -9), "-")(1)
 
        End With
    Next a
Next i
 
End Sub

seule la 4ème méthode gère le cas où tu n'as pas de texte encadré par deux "-"
pour les 3 autres ... je n'ai fais que transposer ta formule qui à la base ne gère pas le cas, et renvoi #VALEUR quand la formule tombe en erreur

quand tu seras à l'aise avec ce que je t'ai proposé, on verra pour te proposer l'optimisation ultime : ne pas utiliser deux boucles pour remplir tes cellules .... il est possible de tout réaliser sans boucle et en 5 lignes de code

**Pierre67** · 01/08/2015, 12h10

si j'ai 10 colonnes je vais dans mon écrire 10 fois la même formule

c'est pas un problème en soi, c'est pas forcement un code "joli"

mais surtout j'essaye de progresser dans l’écriture de mes codes

**SfJ5Rpw8** · 01/08/2015, 12h14

Envoyé par Pierre67

si j'ai 10 colonnes je vais dans mon écrire 10 fois la même formule

c'est pas un problème en soi, c'est pas forcement un code "joli"

mais surtout j'essaye de progresser dans l’écriture de mes codes

Oui mais si c'est 10 fois la même formule pourquoi veut tu la modifier ? tu écris simplement la même chose dans toutes tes colonnes ...c'est le numéro de colonne ou tu veux écrire qui doit être modifié pas ta formule ...

**Pierre67** · 01/08/2015, 12h29

cela m'aurai juste permis de mieux manipuler par code les s formules avec RC[-x]
pour ma culture personnelle

**joe.levrai** · 01/08/2015, 12h57

Tu n'as pas trouvé ton bonheur dans ce que je t'ai proposé ?

Ou c'est parce que je ne t'ai pas montré la méthode avec .FormulaArray ?

**Pierre67** · 01/08/2015, 13h04

Mercie Joe

je vais regarder cela demain car maintenant corvée de courses

bon we

Invité · 01/08/2015, 13h45

Bonjour,
si il y a quelqu’un qui nous a montré l'intérêt et la puissance de formula r1c1, c'est bien Joe.

dans sont exemple post #7 il démontre que le seul cas où la formule reste identique c'est bien le cas de formular1c1 .FormulaR1C1 = "=MID(RC[-9],(SEARCH(""-"",RC[-9],1))+1,SEARCH(""-"",RC[-9],(SEARCH(""-"",RC[-9],1))+1)-((SEARCH(""-"",RC[-9],1))+1))"!

en effet, FormulaR1C1 travail en position relative, R représente la position de la linge qui reçoit la formule décalé de +/- R et C la position de la colonne qui reçoit la formule décalé de +/- C!

la double boucle a et i commence à la ligne 4 et à la colonne 12 cells(4,12) et finie a la ligne 79 et la colonnes 13 cells(79,13)FormulaR1C1 permet de renseigner la plage toute entière sans fair de boucle!
range(cells(4,12),cells(79,13)).FormulaR1C1 = "=MID(RC[-9],(SEARCH(""-"",RC[-9],1))+1,SEARCH(""-"",RC[-9],(SEARCH(""-"",RC[-9],1))+1)-((SEARCH(""-"",RC[-9],1))+1))"