Mise en place d'une boucle "Formula"

[zuli3n]

Bonjour le forum,

J'ai récupéré un bout de VBA sur le forum et je voudrais me l'adapter pour mon exemple.
Etant pas du tout performant en VBA je me tourne vers vous.

Comment faire une boucle avec ça ? :

Private Sub Worksheet_Change(ByVal Target As Range)

If Sheets("test").Range("A4").Value = "" Then
Else
Sheets("test").Range("B4").Formula = "=C4"

End If

If Sheets("test").Range("A5").Value = "" Then
Else
Sheets("test").Range("B5").Formula = "=C5"

End If

If Sheets("test").Range("A6").Value = "" Then
Else
Sheets("test").Range("B6").Formula = "=C6"

End If

If Sheets("test").Range("A7").Value = "" Then
Else
Sheets("test").Range("B7").Formula = "=C7"

End If

If Sheets("test").Range("A8").Value = "" Then
Else
Sheets("test").Range("B8").Formula = "=C8"

End If

If Sheets("test").Range("A9").Value = "" Then
Else
Sheets("test").Range("B9").Formula = "=C9"

End If

End Sub

Je sais... c'est moche

En vous remerciant,
Cordialement.

 

[Dranreb]

Re : Mise en place d'une boucle "Formula"

Bonjour.
Essayez comme ça :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Me.[D4:D9].FormulaR1C1 = "=1/(RC1<>"""")"
On Error Resume Next
Intersect(Me.[B4:B9], Me.[D4:D9].SpecialCells( _
   xlCellTypeFormulas, 1).EntireRow).FormulaR1C1 = "=RC3"
Me.[D4:D9].ClearContents
Application.EnableEvents = True
End Sub

 

[zuli3n]

Re : Mise en place d'une boucle "Formula"

Bonjour Dranreb,

Merci beaucoup pour ce code, qui faut l'avouer, est beaucoup plus classe
Il fonctionne bien, mais je le comprends pas ceci:

Me.[D4:D9].FormulaR1C1 = "=1/(RC1<>"""")"
et
xlCellTypeFormulas, 1).EntireRow).FormulaR1C1 = "=RC3"

Si vous pouvez m'expliquer ça serait un plus pour moi afin que puisse apprendre le truc

Encore merci à vous,
Cordialement.

 

[Dranreb]

Re : Mise en place d'une boucle "Formula"

Alors.
La formule détermine d'abord si la cellule de la colonne A sur la même ligne diffère d'une chaine vide. VRAI ou FAUX. Elle divise ensuite 1 par cela, ce qui l'oblige à convertir ce Boolean en numérique. 1 ou 0. Le résultat de cette division est donc 1 ou #DIV/0!.
Me.[D49].SpecialCells(xlCellTypeFormulas, 1) ne retient de la plage que les cellules contenant une formule renvoyant une valeur numérique, à l'exclusion de toutes autres, notamment les valeurs d'erreurs. Il ne reste plus qu'à garnir de la formule appropriée l'intersection de la colonne B avec les lignes entières de ces cellules retenues.

 

[zuli3n]

Re : Mise en place d'une boucle "Formula"

Re Dranreb,

Merci pour tes explications mes ca n'a pas suffit pour que je comprenne
J'ai essayé d'intégré ton bout de code dans mon fichier final, en vain. Après avoir essayé pas mal de chose j'ai toujours une erreur. J'ai refais un fichier exemple en reproduisant cette fois la partie à calculer dans les lignes et colonnes de mon fichier final.

Voici ce que j'ai essayé:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Me.[M13:M33].FormulaR9C10 = "=1/(RC6>"""")"
On Error Resume Next
Intersect(Me.[H13:H33], Me.[M13:M33].SpecialCells( _
   xlCellTypeFormulas, 1).EntireRow).FormulaR9C10 = "=RC8"
Me.[M13:M33].ClearContents
Application.EnableEvents = True
End Sub

En te remerciant,
Cordialement.

 

[Dranreb]

Re : Mise en place d'une boucle "Formula"

Il n'existe pas de propriété FormulaR9C10.
Voici les 4 qui existent pour affecter une formule exprimée dans chacune des notations existantes dont le nom suit :
Formula : Notation A1 native,
FormulaLocal : Notation A1 locale (française),
FormulaR1C1 : Notation R1C1,
FormulaR1C1Local : Notation L1C1.

 

[zuli3n]

Re : Mise en place d'une boucle "Formula"

Re,

Il existe surement une autre boucle possible avec mon premier bout de vba tout moche qui ne fonctionne pas.
Je vais continué à chercher.

Cordialement.

 

[Dranreb]

Re : Mise en place d'une boucle "Formula"

Pourquoi ? Y-a-t-il autre chose qui ne va pas après avoir remplacé FormulaR9C10 par FormulaR1C1 ?
Ah oui, vous avez mis "=1/(RC6>"""")" au lieu de "=1/(RC6<>"""")" et d'autre part vous mettez en H FormulaR1C1 = "=RC8" ce qui correspond à la colonne H. Référence circulaire.

D'ailleurs pourquoi ne faites vous pas comme ça :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Me.[M13:M33].FormulaR1C1 = "=1/(RC6<>"""")"
On Error Resume Next
Intersect(Me.[H13:H33], Me.[M13:M33].SpecialCells( _
   xlCellTypeFormulas, 1).EntireRow).FormulaR1C1 = "=RC6*R9C10"
Me.[M13:M33].ClearContents
Application.EnableEvents = True
End Sub

Ainsi vous n'auriez plus besoin de la formule en L dont vous écrivez au dessus qu'elle sera cachée.

 

[Si...]

Re : Mise en place d'une boucle "Formula"

salut

avec

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = 0
  [H13:H33].FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-2]*R9C10)"
  Application.EnableEvents = 1
End Sub

tu as la formule dans chaque cellule de la plage.

Avec une ligne de plus, tu n'as que les valeurs et tu n'as plus besoin de la colonne intermédiaire,

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents =0
  [H13:H33].FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-2]*R9C10)"
  [H13:H33] = [H13:H33].Value
  Application.EnableEvents = 1
End Sub

Pour avoir la "Formule" dans le code, écris la dans une cellule, lance l'enregistreur de macros, valide la saisie, arrête l'enregistrement et va voir dans le module créé.

 

[zuli3n]

Re : Mise en place d'une boucle "Formula"

Bonjour Dranreb et Si...,

Merci beaucoup pour vos réponse, très technique il faut l'avouer. J'essais tout ça des que possible, en effet je suis encore dans le coltard, je sors d'une opération ambulatoire de ce matin, je vous tiens au courant des que j'intègre ca dans mon fichier. Encore merci.

Cordialement.

Edit (31/12): Je viens de tester et ça fonctionne très bien, merci à vous deux c'est très sympa. Meilleurs vœux pour 2014.