Donc, en gros, je veux écrire une formule qui vérifiera une condition, et si cette condition est remplie, alors je veux coller une ligne de texte spécifique dans une cellule différente. Je dois noter que je ne veux pas que la formule existe dans la cellule dans laquelle je veux coller une valeur. Ainsi, par exemple, si je veux coller une valeur dans B5, je ne veux pas que la formule soit dans la cellule B5…
Réponses (4)
L'approche suivante utilise une solution de contournement décrite ici et ici pour permettre à une fonction de feuille de calcul définie en VBA de fixer la valeur d'une autre cellule.
La fonction personnalisée stocke dans des variables globales l'adresse de la cellule cible et la valeur à laquelle cette cellule doit être définie. Ensuite, une macro qui est déclenchée lorsque la feuille de calcul recalcule lit les variables globales et fixe la cellule cible à la valeur spécifiée.
L'utilisation de la fonction personnalisée est simple :
=SetCellValue(target_cell, value)
où target_cell est une chaîne de référence à une cellule de la feuille de calcul (par exemple, “A1”) ou une expression qui évalue une telle référence. Cela inclut une expression telle que =B14 où la valeur de B14 est “A1”. La fonction peut être utilisée dans n'importe quelle expression valide.
SetCellValue renvoie 1 si la valeur est écrite avec succès dans la cellule cible, et 0 sinon. Tout contenu précédent de la cellule cible est écrasé.
Trois morceaux de code sont nécessaires :
- le code définissant
SetCellValuelui-même - la macro qui est déclenchée par l'événement de calcul de la feuille de calcul ; et
- une fonction utilitaire
IsCellAddresspour garantir quetarget_cellest une adresse de cellule valide.
Code pour la fonction SetCellValue
Ce code doit être collé dans un module standard inséré dans le classeur. Le module peut être inséré via le menu de l'éditeur Visual Basic, auquel on accède en sélectionnant Visual Basic dans l'onglet Developer du ruban.
Option Explicit
Public triggerIt As Boolean
Public theTarget As String
Public theValue As Variant
Function SetCellValue(aCellAddress As String, aValue As Variant) As Long
If (IsCellAddress(aCellAddress)) And _
(Replace(Application.Caller.Address, "$", "") <> _
Replace(UCase(aCellAddress), "$", "")) Then
triggerIt = True
theTarget = aCellAddress
theValue = aValue
SetCellValue = 1
Else
triggerIt = False
SetCellValue = 0
End If
End Function
Feuille de travail_Calculer le code macro
Ce code doit être inclus dans le code spécifique à la feuille de travail dans laquelle vous utiliserez SetCellValue. Le plus simple est de cliquer avec le bouton droit de la souris sur l'onglet de la feuille de calcul dans la vue Home, de sélectionner View Code, puis de coller le code dans le volet de l'éditeur qui apparaît.
Private Sub Worksheet_Calculate()
If Not triggerIt Then
Exit Sub
End If
triggerIt = False
On Error GoTo CleanUp
Application.EnableEvents = False
Range(theTarget).Value = theValue
CleanUp:
Application.EnableEvents = True
Application.Calculate
End Sub
Code pour la fonction IsCellAddress
Ce code peut être collé dans le même module que le code SetCellValue.
Function IsCellAddress(aValue As Variant) As Boolean
IsCellAddress = False
Dim rng As Range ' Input is valid cell reference if it can be
On Error GoTo GetOut ' assigned to range variable
Set rng = Range(aValue)
On Error GoTo 0
Dim colonPos As Long 'convert single cell "range" address to
colonPos = InStr(aValue, ":") 'single cell reference ("A1:A1" -> "A1")
If (colonPos <> 0) Then
If (Left(aValue, colonPos - 1) = _
Right(aValue, Len(aValue) - colonPos)) Then
aValue = Left(aValue, colonPos - 1)
End If
End If
If (rng.Rows.Count = 1) And _
(rng.Columns.Count = 1) And _
(InStr(aValue, "!") = 0) And _
(InStr(aValue, ":") = 0) Then
IsCellAddress = True
End If 'must be single cell address in this worksheet
Exit Function
GetOut:
End Function
``` ### Code pour la fonction IsCellAddress
Supposons que vous vouliez que le texte “Texte A” apparaisse dans la cellule C5 si la cellule B5 contient la valeur “vert”.
Vous pouvez utiliser une approche par formule, mais comme les formules ne peuvent pas changer les valeurs dans les autres cellules, la formule devra être entrée dans la cellule C5.
=IF(ISNUMBER(FIND("green",B5)),"Text A","")
La cellule C5 n'affichera désormais le “Texte A” que si B5 contient le mot “vert”.
Une formule comme celle-ci peut être construite pour fonctionner dans de nombreuses conditions. Vous devez définir vos besoins afin d'obtenir de l'aide pour votre situation spécifique.
Si vous ne voulez pas que C5 ait une formule, vous pouvez également utiliser une approche VBA. Vous pouvez lancer un événement de modification de la feuille de calcul qui s'exécutera chaque fois que la cellule B5 sera modifiée, soit en modifiant manuellement la valeur, soit en y collant quelque chose.
Un exemple pour une telle macro pourrait être
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
If InStr(1, Target, "green", vbTextCompare) Then
Target.Offset(0, 1) = "Text A"
End If
End If
End Sub
Les conditions et le placement de la sortie ne sont bien sûr qu'un exemple et doivent être adaptés à vos besoins.
La différence entre la formule et l'approche macro est
- avec l'approche par formule, la cellule C5 contiendra une formule. Si l'utilisateur supprime accidentellement la formule, la fonctionnalité qu'elle fournit sera également supprimée. (Il existe cependant des moyens de gérer cela)
- avec la macro VBA, la cellule C5 n'affichera aucune formule et aura le texte intégral comme valeur, mais la modification de la configuration nécessite la connaissance d'Excel VBA. En outre, avec une approche VBA, le classeur doit être enregistré comme un classeur avec macros et l'utilisateur doit autoriser les macros ou faire du fichier un fichier de confiance.
Note : ce qui précède n'est qu'un exemple. Vous devez définir vos besoins, que ce soit pour évaluer des chiffres ou du texte, si l'évaluation est sensible à la casse, quelles sont les règles d'évaluation, où placer le résultat, etc.
Pour vérifier si une condition est remplie, inscrivez une formule IF dans une cellule pour mettre à jour “une ligne de texte spécifique dans une autre cellule”. La cellule de résultat ne contiendra que la valeur de la formule, et NON la formule qui a généré cette valeur, comme c'est le cas :
Cellule de travail (ex. J5) =IF(A1="yes","Specific line of text","")
Cellule de résultat (ex. B5) =J5
Donc si la condition est remplie (A1=“oui”), B5 contiendra “une ligne de texte spécifique”. Sinon, elle reste vide.
Notes: Les cellules qui ont des valeurs variables contiennent normalement une sorte de formule pour continuer à mettre à jour leurs valeurs.
Si vous voulez que personne ne connaisse la formule réelle qui crée la valeur, la formule peut aller dans une autre cellule pour générer la valeur, qui sera ensuite copiée dans la cellule de résultat, par exemple B5.
Pour cacher la formule qui a généré la valeur ou pour cacher la cellule à laquelle B5 se réfère (pour continuer à vérifier si la valeur doit être mise à jour), reportez-vous à https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f
Les autres fonctions qui peuvent obtenir le même effet comprennent CHOOSE, HLOOKUP, LOOKUP, VLOOKUP.
=valeur(cellule)
Formule simple à utiliser, avait le même problème et ça marche.