VBA : Utiliser le résultat d'une InputBox comme critère dans une formule

[Shady69]

Bonjour à toutes et à tous,

Je viens vers vous après de nombreuses tentatives soldées par des échecs.
Je suis en train de préparer un fichier de calcul pour mon travail.
Je suis novice en VBA, mais grâce à l'enregistreur et aux multiples sujets sur les forums j'ai pu avancer mon fichier sans trop de soucis.. Jusqu'à maintenant !
Je bloque sur la syntaxe pour utiliser le résultat d'une Inputbox dans une formule SUMIFS

Ci-dessous mon InputBox :

periodetraitee = InputBox("../../....", "A quelle date voulez-vous traiter le document ?")
anneetraitee = Year(periodetraitee)
Sheets("Notice").Range("N2") = periodetraitee

Comme vous le comprendrez, cette InputBox renvoie donc une date que j'affecte ensuite à là cellule N2 de ma feuille "Notice". Jusque là, tout va bien.

Mon problème survient ensuite.
Comment puis-je utiliser "periodetraitee" ou "anneetraitee" dans une formule ?

Je souhaite chercher des éléments inférieure à cette date, donc pour l'instant j'ai écris ceci dans ma formule :

""<""&""31/12/""&YEAR(Notice!R2C14))

Si ma date en N2 est par exemple le 31/05/2018, mon calcul ne prendra que les éléments < 31/12/2017.
Ca, ça marche, aucune soucis.

Mais j'aimerais, si possible, remplacer le code ci-dessus par quelque chose comme :

""<""&""31/12/""&anneetraitee

J'ai essayé mais ça ne fonctionne pas.. Je n'ai pas de message d'erreur lors de l’exécution de ma macro, mais les calculs ne se font pas. A la place, la valeur "#NOM?" est renvoyée.

Pouvez-vous m'aider à trouver la bonne syntaxe svp ??

Merci d'avance pour le temps accordé à ce post

EDIT : Je n'ai pas parlé de "periodetraitee" mais je compte m'en servir aussi, donc non, cette variable n'est pas inutile lol

 

[Dranreb]

Bonjour.
Ça donne quoi avec """<" & DateSerial(AnnéeTraitée, 12, 31) & """" ?

 

[Shady69]

Bonjour Dranreb, et merci pour cette réponse rapide.

Malheureusement ça ne passe pas non plus.. Pas de message d'erreur, mais la cellule renvoie maintenant la valeur "FAUX"

 

[Dranreb]

C'était bien une expression au moins, juste une partie d'une formule ?
Joignez le classeur qu'on voie ce que vous essayez de faire.

 

[Shady69]

Oui, cela faisait partie d'une formule, là voici en entière :

Range("K" & i).Select
    ActiveCell.FormulaR1C1 = "=IF(RC[3]=""Actif"",SUMIFS('Transactions objets'!C[-2],'Transactions objets'!C[-10],'RECAP IMMOS'!RC[-9],'Transactions objets'!C[-5],""DEPRECIATION"",'Transactions objets'!C,""Prêt"",'Transactions objets'!C[-8],""<""&""01/01/""&YEAR(Notice!R2C14)),0)"

(J'ai changé 31/12 en 01/01, c'était une erreur de ma part).

Je joindrai un classeur anonymisé, mais cela va prendre un peu de temps, je travaille sur 55.000 lignes, donc pour simplifier et anonymiser en gardant le principe du fichier cela risque de prendre du temps)

PS : j'ai oublié de préciser que je travaille sur Excel 2013

 

[Dranreb]

Et avec """<"" & CDbl(DateSerial(AnnéeTraitée, 12, 31)) ça donne quoi ?
En général quand on concatène à "<" une date les "/" disparaissent, c'est le numéro de série du jour de la date qui est pris.

 

[Shady69]

Je teste ça dans un moment et je reviens vers vous.
Je ne connais pas CDbl, si j'ai bien compris il s'agit de convertir le numéro de série au format date c'est bien ça ?

 

[Staple1600]

Bonsoir le fil, le forum


Juste à titre d'exemple

Sub b()
Dim dDate
dDate = InputBox("Saisir une date", "Saisie", Date)
[C1] = dDate
[A1].Formula = "=MONTH(" & Chr(34) & dDate & Chr(34) & ")"
[B1].FormulaR1C1 = "=DATE(YEAR(RC[1]),MONTH(RC[1])+1,0)"
[B2].Formula = "=DATE(YEAR(C1),MONTH(C1)+1,0)"
End Sub

Les formules fonctionnent correctement.

 

[Dranreb]

Je ne connais pas CDbl, si j'ai bien compris il s'agit de convertir le numéro de série au format date c'est bien ça ?

Non, au contraire CDbl convertit une date en le nombre de jour écoulés depuis le 1er janvier 1900.
Pourquoi je suggère de faire ça ? :
Dans une feuille que j'avais sous la main j'ai mis :
En F7 :

=AUJOURDHUI()

En F11 :

=F7-5

En F12, propagé sur 8 lignes :

=F11+1

Enfin en F9 :

=NB.SI(F$11:F$19;"<"&$F$7)

Cette dernière formule me répond 5. C'est bon, c'est normal.
Maintenant dans ma formule je sélectionne l'expression "<"&$F$7 et je fais F9 pour la remplacer par sa valeur.
Voilà ce qui en résulte:En F9 :

=NB.SI(F$11:F$19;"<43270")

et non pas :

=NB.SI(F$11:F$19;"<19/06/2018")

 

[Shady69]

Bonjour,

Staples1600, merci pour ces précisions. Mon problème survient lorsque je veux utiliser une date stockée dans la variable "anneetraitee"

Dranreb, merci également pour cette piste, et pour l'explication sur CDbl.
J'ai remplacé

""<01/01/2018""

par

""<"" & CDbl(DateSerial(AnnéeTraitée, 1, 1))

Mais ça ne fonctionne toujours pas, la macro tourne mais je renvoie pas de valeur (j'ai essayé de bidouillé, j'obtiens soit 0 soit #NOM? lorsque je fais F9).

Je comprends qu'il n'est pas facile de comprendre mon soucis sans fichier. J'essaierai ce soir ou demain de l'anonymiser.

 

[Dranreb]

Il est surtout impossible de vérifier nos propositions. À la réflexion les deux doubles apostrophes en fin de ""<"" sont sans doutes incorrectes. Il n'en faudrait qu'une pour que l'expression CDbl etc. soit traitée. Par contre il faudrait faire suivre l'expression de & """…

 

[Staple1600]

Bonsoir le fil, le forum

Bonjour à toutes et à tous,
Je suis en train de préparer un fichier de calcul pour mon travail.

Dans ce cas, prépare-nous une copie anonymisée de ton fichier et poste-le ici 😉
On devrait finir par y voir plus clair 😉

 

[Shady69]

Bonjour à vous deux.

J'ai finalement opté pour une solution plus simple !
N'arrivant pas à utiliser la variable "anneetraitee" dans ma formule, j'ai fixé sa valeur dans une cellule sur une nouvel feuille. J'utilise ensuite le Range où se situe la valeur, puis je supprime l'onglet à la fin de ma macro.

C'est sûrement un peu moins propre comme ça, mais ça marche !

 

[Dranreb]

Bonsoir

C'est sûrement un peu moins propre comme ça,

C'est même carrément lamentable, oui !
Pour un problème si simple…

En fait ces 3 solutions marchent chez moi et donnent le même résultat :

Sub test()
Dim Année As Long
Année = InputBox("Année")
Feuil1.[D4].FormulaR1C1 = "=COUNTIF(R2C2:R27C2,""<" & CDbl(DateSerial(Année, 1, 1)) & """)"
Feuil1.[E4].FormulaR1C1 = "=COUNTIF(R2C2:R27C2,""<""&DATE(" & Année & ",1,1))"
Feuil1.[F4].FormulaR1C1 = "=COUNTIF(R2C2:R27C2,""<" & DateSerial(Année, 1, 1) & """)"
End Sub

Et si vous nous indiquiez la formule entière que vous voulez mettre en place, telle qu'elle devra figurer dans la cellule, hein ?

 

[Dranreb]

Et ces deux instructions posent aussi des formules syntaxiquement correctes :

Feuil1.[D6].FormulaR1C1 = "=IF(RC[3]=""Actif"",SUMIFS('Transactions objets'!C[-2],'Transactions objets'!C[-10]," _
   & "'RECAP IMMOS'!RC[-9],'Transactions objets'!C[-5],""DEPRECIATION"",'Transactions objets'!C,""Prêt""," _
   & "'Transactions objets'!C[-8],""<01/01/" & Année & """),0)"
Feuil1.[E6].FormulaR1C1 = "=IF(RC[3]=""Actif"",SUMIFS('Transactions objets'!C[-2],'Transactions objets'!C[-10]," _
   & "'RECAP IMMOS'!RC[-9],'Transactions objets'!C[-5],""DEPRECIATION"",'Transactions objets'!C,""Prêt""," _
   & "'Transactions objets'!C[-8],""<""&DATE(" & Année & ",1,1)),0)"