Formule à convertir en VBA
Je viens de modifier la formule, à toi de tester :
Sub Test()
Dim Formule As String
Formule = "=IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,"
Formule = Formule & "COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0,COUNTIF(H$35:H35,""cmo"")>0,COUNTIF(H$35:H35,32)>0,"
Formule = Formule & "COUNTIF(H$35:H35,2)>0),""F"",IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,"
Formule = Formule & "COUNTIF(H$35:H35,38)>0,COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0,COUNTIF(H$35:H35,""cmo"")>0,"
Formule = Formule & "COUNTIF(H$35:H35,32)>0),2,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,"
Formule = Formule & "COUNTIF(H$35:H35,38)>0,COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0,COUNTIF(H$35:H35,""cmo"")>0),32,"
Formule = Formule & "IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,COUNTIF(H$35:H35,35)>0,"
Formule = Formule & "COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0),""cmo"",IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,"
Formule = Formule & "COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0),36,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,"
Formule = Formule & "COUNTIF(H$35:H35,35)>0),37,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0),35,IF(AND(COUNTIF(H$35:H35,1)>0,"
Formule = Formule & "COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0),38,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0),39,IF(AND(COUNTIF(H$35:H35,1)>0,"
Formule = Formule & "COUNTIF(H$35:H35,33)>0),30,IF(COUNTIF(H$35:H35,1)>0,33,1)))))))))))"
Range("H36:BT127").Formula = Formule
End Sub
Hello Theze,
Parfait, elle s'incrit bien sur les cellules ! mais comment la rendre invisible?
Merci
Setila
Est-il possible de faire en sorte d'appliquer une condition sur cette formule afin qu'elle s'applique uniquement sur les cases blanches (créées par MFC) ?
Quelque chose du genre :
IF Case = white
Formule = "=IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,"
Formule = Formule & "COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0,COUNTIF(H$35:H35,""cmo"")>0,COUNTIF(H$35:H35,32)>0,"
Formule = Formule & "COUNTIF(H$35:H35,2)>0),""F"",IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,"
Formule = Formule & "COUNTIF(H$35:H35,38)>0,COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0,COUNTIF(H$35:H35,""cmo"")>0,"
Formule = Formule & "COUNTIF(H$35:H35,32)>0),2,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,"
Formule = Formule & "COUNTIF(H$35:H35,38)>0,COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0,COUNTIF(H$35:H35,""cmo"")>0),32,"
Formule = Formule & "IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,COUNTIF(H$35:H35,35)>0,"
Formule = Formule & "COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0),""cmo"",IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,"
Formule = Formule & "COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0),36,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,"
Formule = Formule & "COUNTIF(H$35:H35,35)>0),37,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0),35,IF(AND(COUNTIF(H$35:H35,1)>0,"
Formule = Formule & "COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0),38,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0),39,IF(AND(COUNTIF(H$35:H35,1)>0,"
Formule = Formule & "COUNTIF(H$35:H35,33)>0),30,IF(COUNTIF(H$35:H35,1)>0,33,1)))))))))))"
Range("H36:BT127").Formula = Formule
ELSE = rien
Globalement, les cases s'affichent en blanc si l'heure associée est comprise entre les deux valeurs sur le côté (voir MFC).
Merci d'avance,
Setila
Hello Steelson,
Oui je suis d'accord avec toi! je cherche qqch de simple!
Je viens d'essayer en nomant la zone mais ça ne fonctionne pas!
Merci
Setila
Bonjour,
A tester, en H36 :
=SIERREUR(INDEX({1;33;30;39;38;35;37;36;"cmo";32;2};MIN(SI(NB.SI(H$35:$H35;{1;33;30;39;38;35;37;36;"cmo";32;2});{99;99;99;99;99;99;99;99;99;99;99};LIGNE($1:$11))));"F")
formule matricielle à valider avec shift+ctrl+entrée
A tirer vers le bas. Si des formules sont supprimées (cellules grises), les suivantes s'adaptent et reprennent à l'interruption.
Si un n° de caisse est forcé manuellement (comme en ligne 35), il se trouve exclu ensuite.
eric
Hello eriic,
Merci pour tout aide!
Ta formule fonctionne mais quand je supprime la caisse 1 et n'est pas réatribuer?! j'ai l'impression que la formule ne tiens pas compte de la plage!
Aprés le code de Theze fonctionne mais j'aimerais que la formule apparaise uniquement sur les cellules de couleur blanches.
Et que les formules ne soient pas visible !
Merci
Setila
Ta formule fonctionne mais quand je supprime la caisse 1 et n'est pas réatribuer?!
chez moi si
j'ai l'impression que la formule ne tiens pas compte de la plage!
c'est une impression oui
eric
Hello Eriic,
Désolé pour le retard mais déplacement oblige!!!
J'ai bien retester ta formule et elle fonctionne parfaitement!!!
Comment la mettre en VBA et peut par la même occasion ajouter ce code ci-dessous afin de supprimer les formule qui se mettent en cellules grises :
Sub delete()
Dim Sh As Worksheet
For Each Sh In Worksheets
With Sh
For Each Vcel In .Range("H35:BT127")
If Vcel.DisplayFormat.Interior.Color = RGB(128, 128, 128) Then
Vcel.Value = ""
End If
Next Vcel
End With
Next
End Sub
Un grand merci Eriic
Bonne journée,
Setila
Hello Steelson,
Merci pour le fichier.
Comme sur la capture d'écran lorsque je rentre une caisse qui est déjà attribué alors je me retrouve avec 2 caisses les mêmes!
La formule fonctionne jusqu'à se qu'on le fasse manuellement et là les caisses ne se réattribue plus correctement.
Merci beaucoup
Bonne journée,
Setila
Bonjour,
afin de supprimer les formule qui se mettent en cellules grises
le plus rapide est de ne rien mettre du tout vu que TOUTES tes cellules sont grises.
Celles apparaissant en blanc le sont par une MFC...
J'ai donc supprimé tes 3 MFC. La couleur doit être mise manuellement.
Par ailleurs, la formule partait du principe que tu remplissais manuellement la ligne 35 comme sur ton exemple.
Si ce n'est plus le cas, il lui faut quand même une ligne vide à insérer en 35 sinon on part sur une référence circulaire impossible à gérer.
Je démarre donc en ligne 36
D'autre part le "F" ne peut être à la fois le 1er et le dernier. J'ai fait le choix en dernier, et il se répète tant qu'il y a des lignes à remplir.
En premier il ne peut être mis que manuellement.
Sub poseFormule()
Dim pl As Range, pl2 As Range, c As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set pl = Range("H36:BT127")
pl.ClearContents
For Each c In pl
If c.Interior.ColorIndex = xlNone Then
If pl2 Is Nothing Then Set pl2 = c Else Set pl2 = Union(pl2, c)
End If
Next c
pl2.Formula = "=IFERROR(INDEX({1;33;30;39;38;35;37;36;""cmo"";32;2},MIN(IF(COUNTIF(R35C:R[-1]C,{1;33;30;39;38;35;37;36;""cmo"";32;2}),{99;99;99;99;99;99;99;99;99;99;99},ROW(R1:R11)))),""F"")"
For Each c In pl2
c.FormulaArray = c.Formula
Next c
Application.Calculation = xlCalculationAutomatic
Set pl = Nothing: Set pl2 = Nothing: Set c = Nothing
End Sub
Si tu as peu de gris il faut un certain temps pour la mise en place. 12 s chez moi
eric
Hello à tous,
Le code ci dessous fonctionne mais ne s'applique pas à tout les onglets ?
Et le code rame un peu ? qq'un peu m' aider à l'optimiser et à l'appliquer sur tout les onglets?
Sub Test()
Dim Formule As String
Dim Sh As Worksheet
For Each Sh In Worksheets
With Sh
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Range("H36:BT127").Formula = Formule
Formule = "=IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,"
Formule = Formule & "COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0,COUNTIF(H$35:H35,""cmo"")>0,COUNTIF(H$35:H35,32)>0,"
Formule = Formule & "COUNTIF(H$35:H35,2)>0),""F"",IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,"
Formule = Formule & "COUNTIF(H$35:H35,38)>0,COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0,COUNTIF(H$35:H35,""cmo"")>0,"
Formule = Formule & "COUNTIF(H$35:H35,32)>0),2,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,"
Formule = Formule & "COUNTIF(H$35:H35,38)>0,COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0,COUNTIF(H$35:H35,""cmo"")>0),32,"
Formule = Formule & "IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,COUNTIF(H$35:H35,35)>0,"
Formule = Formule & "COUNTIF(H$35:H35,37)>0,COUNTIF(H$35:H35,36)>0),""cmo"",IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,"
Formule = Formule & "COUNTIF(H$35:H35,35)>0,COUNTIF(H$35:H35,37)>0),36,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0,"
Formule = Formule & "COUNTIF(H$35:H35,35)>0),37,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0,COUNTIF(H$35:H35,38)>0),35,IF(AND(COUNTIF(H$35:H35,1)>0,"
Formule = Formule & "COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0,COUNTIF(H$35:H35,39)>0),38,IF(AND(COUNTIF(H$35:H35,1)>0,COUNTIF(H$35:H35,33)>0,COUNTIF(H$35:H35,30)>0),39,IF(AND(COUNTIF(H$35:H35,1)>0,"
Formule = Formule & "COUNTIF(H$35:H35,33)>0),30,IF(COUNTIF(H$35:H35,1)>0,33,1)))))))))))"
For Each Vcel In Range("H35:BT127")
If Vcel.DisplayFormat.Interior.Color = RGB(128, 128, 128) Then
Vcel.Value = ""
End If
Next Vcel
End With
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
Merci d'avance,
Setila
Pas d'erreur chez moi.
Passe moi l'expression, mais il faut être un peu tordu pour mettre toutes les cellules en gris pour les remettre en blanc avec une MFC.
Surtout sur une plage aussi vaste. Je te rappelle que les MFC sont volatiles et recalculées à chaque saisie.
Rien de tel pour apporter des latences rapidement insupportables.
Ici je défini la plage qui recevra les formules :
If c.Interior.ColorIndex = xlNone Then
If pl2 Is Nothing Then Set pl2 = c Else Set pl2 = Union(pl2, c)
End If
Ajoutes-y toutes les conditions que tu veux.
De plus ta MFC gris clair est illogique avec ta demande.
Soit c'est le gris (mis manuellement) qui impose de ne pas mettre de formule, soit le vide qui met en gris par la MFC.
Ca ne peut pas être les deux...
J'ai l'impression que tu n'as pas trop réfléchi au fonctionnement voulu.
En résumé il ne doit rester que la MFC noire.
eric
Edit : bon ok. Je vois que tu es passé sur une autre solution c'est donc fini pour moi.
Hello Eriic,
Je te confirme que chez moi il a une erreur!
j'espère que tu peux comprendre que nous n'avons pas tous les mêmes connaissance ainsi que ton niveau d'expérience?!
Je suis ici pour apprendre et comprendre!
Je ne me limite pas à une solution.
Comme tu as pu le voir, la mfc fonctionne par rapport à la plage horaire des personnes et le gris est mis en contraste tout simplement! si il existe une manière plus simple et plus logique, je suis preneur mais mon niveau ne me le permet pas, c'est pourquoi je fais appel à vous.
J'ai bien réflechis à ma demande et ce que je veux c'est d'appliquer uniquement une formule dans les cellules blanches créé par mfc qui peuvent changer avec les horaires et si possible en vba???
Merci pour ton aide et ta patience
Bonne soirée
Setila
Hello Eriic,
Je tiens à te présenter mes excuses pour mon impatience!!!
En effet, après une bonne semaine de repos, j'ai de nouveau regardé ta formule et elle fonctionne parfaitement!
Vous êtes vraiment incroyable (Forum)...
Un grand merci à toutes les personnes qui m'ont aidé à comprendre et à trouver plusieurs solutions.
Merci beaucoup à Eriic, James007, Steelson, i20100, Theze
Bonne soirée
Setila