Formule très longue à mettre dans une cellule via VBA

Version imprimable

Bonjour,

j'ai une formule, extrêmement longue qui me permet de changer d'heure (heure été, heure hiver) automatiquement sans interférer dans le calcul en fonction d'autres cellules. je vous la donne pour juger de la longueur de cette formule.

Code:

1
2
 
=SI(ET(DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI()))>=DATE(ANNEE(AUJOURDHUI());SI(GAUCHE(B2;CHERCHE(":";B2;1)-1)="0";DROITE(B2;NBCAR(B2)-CHERCHE(":";B2;1))+1;DROITE(B2;NBCAR(B2)-CHERCHE(":";B2;1)));1)-JOUR(DATE(ANNEE(AUJOURDHUI());SI(GAUCHE(B2;CHERCHE(":";B2;1)-1)="0";DROITE(B2;NBCAR(B2)-CHERCHE(":";B2;1))+1;DROITE(B2;NBCAR(B2)-CHERCHE(":";B2;1)));1))+8-JOURSEM(DATE(ANNEE(AUJOURDHUI());SI(GAUCHE(B2;CHERCHE(":";B2;1)-1)="0";DROITE(B2;NBCAR(B2)-CHERCHE(":";B2;1))+1;DROITE(B2;NBCAR(B2)-CHERCHE(":";B2;1)));1)-JOUR(DATE(ANNEE(AUJOURDHUI());SI(GAUCHE(B2;CHERCHE(":";B2;1)-1)="0";DROITE(B2;NBCAR(B2)-CHERCHE(":";B2;1))+1;DROITE(B2;NBCAR(B2)-CHERCHE(":";B2;1)));1))+7)+(GAUCHE(B2;CHERCHE(":";B2;1)-1)*7-7);DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI()))<DATE(ANNEE(AUJOURDHUI());SI(GAUCHE(C2;CHERCHE(":";C2;1)-1)="0";DROITE(C2;NBCAR(C2)-CHERCHE(":";C2;1))+1;DROITE(C2;NBCAR(C2)-CHERCHE(":";C2;1)));1)-JOUR(DATE(ANNEE(AUJOURDHUI());SI(GAUCHE(C2;CHERCHE(":";C2;1)-1)="0";DROITE(C2;NBCAR(C2)-CHERCHE(":";C2;1))+1;DROITE(C2;NBCAR(C2)-CHERCHE(":";C2;1)));1))+8-JOURSEM(DATE(ANNEE(AUJOURDHUI());SI(GAUCHE(C2;CHERCHE(":";C2;1)-1)="0";DROITE(C2;NBCAR(C2)-CHERCHE(":";C2;1))+1;DROITE(C2;NBCAR(C2)-CHERCHE(":";C2;1)));1)-JOUR(DATE(ANNEE(AUJOURDHUI());SI(GAUCHE(C2;CHERCHE(":";C2;1)-1)="0";DROITE(C2;NBCAR(C2)-CHERCHE(":";C2;1))+1;DROITE(C2;NBCAR(C2)-CHERCHE(":";C2;1)));1))+7)+(GAUCHE(C2;CHERCHE(":";C2;1)-1)*7-7));DROITE(D2;NBCAR(D2)-CHERCHE(":";D2;1));GAUCHE(D2;CHERCHE(":";D2;1)-1))

Pourriez vous me donner la solution pour que cette formule puisse etre mise dans une cellule via vba car j'ai essayé avec 2 variables mais cela ne marche pas. Enfin cela fonctionne mais je n'ai pas toute la formule. Le nombre de caractère doit être limité.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Dim i As Integer, formule1 As String, formule2 As String
    i = 1
    formule1 = "=IF(AND(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))>=DATE(YEAR(TODAY()),IF(LEFT(B1,SEARCH("":"",B1,1)-1)=""0"",RIGHT(B1,LEN(B1)-SEARCH("":"",B1,1))+1,"
    formule1 = formule1 & "RIGHT(B1,LEN(B1)-SEARCH("":"",B1,1))),1)-DAY(DATE(YEAR(TODAY()),IF(LEFT(B1,SEARCH("":"",B1,1)-1)=""0"",RIGHT(B1,LEN(B1)-SEARCH("":"",B1,1))+1,"
    formule1 = formule1 & "RIGHT(B1,LEN(B1)-SEARCH("":"",B1,1))),1))+8-WEEKDAY(DATE(YEAR(TODAY()),IF(LEFT(B1,SEARCH("":"",B1,1)-1)=""0"",RIGHT(B1,LEN(B1)-SEARCH("":"",B1,1))+1,"
    formule1 = formule1 & "RIGHT(B1,LEN(B1)-SEARCH("":"",B1,1))),1)-DAY(DATE(YEAR(TODAY()),IF(LEFT(B1,SEARCH("":"",B1,1)-1)=""0"",RIGHT(B1,LEN(B1)-SEARCH("":"",B1,1))+1,"
    formule1 = formule1 & "RIGHT(B1,LEN(B1)-SEARCH("":"",B1,1))),1))+7)+(LEFT(B1,SEARCH("":"",B1,1)-1)*7-7),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))<DATE(YEAR(TODAY()),"
    formule2 = "IF(LEFT(C1,SEARCH("":"",C1,1)-1)=""0"",RIGHT(C1,LEN(C1)-SEARCH("":"",C1,1))+1,RIGHT(C1,LEN(C1)-SEARCH("":"",C1,1))),1)-DAY(DATE(YEAR(TODAY()),IF(LEFT(C1,SEARCH("":"",C1,1)-1)=""0"","
    formule2 = formule2 & "RIGHT(C1,LEN(C1)-SEARCH("":"",C1,1))+1,RIGHT(C1,LEN(C1)-SEARCH("":"",C1,1))),1))+8-WEEKDAY(DATE(YEAR(TODAY()),IF(LEFT(C1,SEARCH("":"",C1,1)-1)=""0"",RIGHT(C1,LEN(C1)" 'c'est ici que cela s'arrete avant les 3 derniers caractères "C1)"
    formule2 = formule2 & "-SEARCH("":"",C1,1))+1,RIGHT(C1,LEN(C1)-SEARCH("":"",C1,1))),1)-DAY(DATE(YEAR(TODAY()),IF(LEFT(C1,SEARCH("":"",C1,1)-1)=""0"",RIGHT(C1,LEN(C1)-SEARCH("":"",C1,1))+1,"
    formule2 = formule2 & "RIGHT(C1,LEN(C1)-SEARCH("":"",C1,1))),1))+7)+(LEFT(C1,SEARCH("":"",C1,1)-1)*7-7)),RIGHT(D1,LEN(D1)-SEARCH("":"",D1,1)),LEFT(D1,SEARCH("":"",D1,1)-1))"
 
 
    MsgBox formule1 & formule2 'ne me donne pas toute la formule (J'ai tout jusqu’à la moitié de formule2)
 
    While Worksheets("Aeroport").Range("A" & i) <> ""
        Worksheets("Aeroport").Range("E" & i).formula = "=IF(AND(B1=""N/A"",C1=""N/A""),D1,Z1)"
        Worksheets("Aeroport").Range("Z" & i).formula = formule1 & formule2 ' ne me donne pas la formule complète (J'ai tout jusqu’à la moitié de formule2)
        i = i + 1
    Wend

J'ai bien vérifié dans mes variables formule, j'ai bien tout ce que je dois avoir.
Merci par avance.

06/09/2017, 11h00
Pierre Fauconnier

1 pièce(s) jointe(s)

Salut.

Elle est très longue, mais inutilement.

Tu peux déjà remplacer DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())) par aujourdhui() :ptdr:

Je n'ai même pas regardé la suite, comme en plus je ne comprends pas l'utilité de la démarche... :roll:

Cela dit, lorsque tu as des chaines de caractères dans une formule et que tu veux recomposer la chaine en VBA, tu dois doubler les " à l'intérieur de la chaine

Pièce jointe 309665
06/09/2017, 12h12
levisagepale

bonjour merci pour le temps passer à me répondre.

pour la remarque je l'ai prise en compte. Malheureusement, il n'y en avait que deux dans la formule, Cela ne l'amaigrit pas trop :_(.

Pour le "" il me semble bien les avoir mis de partout... il ne me donne pas d'erreur.

les changements d'heures se font sous la forme (ex: 1er dimanche d'avril et dernier dimanche d'octobre). Cela change en fonction des pays qui font un changement d'heure.

j'ai une base de données qui me donne:
Dans la colonne A les villes pour lesquelles j'aimerai savoir l'heures qu'il est en temps réelle.
Dans la colonne B le 1er changement d'heure de l'année (donc passage à l'heure d'été) sous la forme: Prenons dans l'exemple ci-dessus: 1:4 (pour 1er dimanche : mois d'avril)
Dans la colonne C le 2eme changement d'heure de l'année (donc passage à l'heure d'hiver) sous la forme: Reprenons dans l'exemple ci-dessus: 0:10 (pour dernier dimanche : mois d'octobre)

pourquoi 0? car certain mois le dernier dimanche c'est le 5eme dimanche et pour d'autres mois c'est le 4eme dimanche et cela change en fonction des années. Alors pour faire la différence entre 4eme dimanche et dernier dimanche j'ai pris le 0 comme valeur et non 4.

Et dans ma troisième colonne, la colonne D j'ai la différence d'heure entre l'heure universelle (l'heure de Greenwich et l'heure local). par exemple, si on est à Paris, on aura: +1:+2

donc ma formule calcul chaque jour quand j'ouvre le fichier l'heure local à la date du jour, avec les références de la base de données. C'est à dire que mon fichier saura, pour Paris, s'il doit ajouter +1 ou +2 heures à l'heure universelle.

Maintenant étant novice, je n'ai trouvé que ce moyen là pour arriver à mes fins. (remarque ce n'est pas la façon d'arriver au résultat mais le résultat qui compte non? XD)

Si vous avez un idée plus rapide pour arriver au même résultat. Je suis preneur.... XD

Merci
06/09/2017, 21h05
eriiic

Bonjour,

En mettant des dates en B:C tu devrais pouvoir simplifier considérablement.
Avec l'année en A1,
Heure d'hiver (le dernier dimanche d'octobre est le 1er dimanche de novembre -7) :

Code:

=7+DATE($A$1;11;1)-JOURSEM(DATE($A$1;11;1)-7;2)-7

Pour l'heure d'été même principe. Je pensais que c'était dernier dimanche de mars mais comme tu l'as mis début avril je n'ai pas fait
eric
07/09/2017, 10h05
levisagepale

Merci Éric d'avoir pris le temps de me répondre.

étant donné qu'il y a un ET dans la formule, j'ai pris la décision de séparer en deux la formule pour la mettre dans deux cellules.

Merci d'avoir quand même réfléchi à mon problème.