Problème avec formulaR1C1

Version imprimable

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:

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

01/08/2015, 00h22
parmi

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 ?
01/08/2015, 09h35
Pierre67

Bonjour

Citation:

'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

Citation:

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:

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
01/08/2015, 11h27
SfJ5Rpw8

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 ?
01/08/2015, 12h00
Pierre67

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
01/08/2015, 12h03
SfJ5Rpw8

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

Bonjour,

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

Code:

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

01/08/2015, 12h10
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
01/08/2015, 12h14
SfJ5Rpw8

Citation:

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 ...
01/08/2015, 12h29
Pierre67

cela m'aurai juste permis de mieux manipuler par code les s formules avec RC[-x]
pour ma culture personnelle:P:P
01/08/2015, 12h57
joe.levrai

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 ? :aie:
01/08/2015, 13h04
Pierre67

Mercie Joe

je vais regarder cela demain car maintenant corvée de courses:oops:
bon we
01/08/2015, 13h45
Invité

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))"
01/08/2015, 15h14
joe.levrai

rdurupt a vendu la mèche avant que tu digères la première étape !!

bye bye les boucles :mrgreen:
01/08/2015, 15h32
Invité

désolé Joe d'avoir bousillé ta stratégie pédagogique!
01/08/2015, 15h39
joe.levrai

Pas de mal, le fossé entre les deux était pas énorme

et puis, j'aurai sûrement utilisé un Resize, toi c'est plus intuitif pour sa compréhension
01/08/2015, 17h06
Pierre67

merci a Joe et a rdurupt pour votre contribution

avec une mention particulière à rrurupt pour son ingéniosité
bon week end à vous

:applo::king::king: