In principe wil ik een formule schrijven die een voorwaarde controleert, en als aan die voorwaarde is voldaan, dan wil ik een specifieke regel tekst in een andere cel plakken. Ik moet opmerken dat ik niet wil dat de formule bestaat in de cel waar ik een waarde in wil plakken. Dus bijvoorbeeld, als ik een waarde in B5 wil plakken, dan wil ik niet dat de formule in cel B5 staat…
Antwoorden (4)
De volgende aanpak maakt gebruik van een workaround die hier en hier zijn beschreven om een in VBA gedefinieerde werkbladfunctie in staat te stellen de waarde van een andere cel in te stellen.
De aangepaste functie slaat in globale variabelen het adres op van de doelcel en de waarde waarop die cel moet worden ingesteld. Vervolgens leest een macro die wordt geactiveerd wanneer het werkblad opnieuw wordt berekend, de globale variabelen uit en stelt de doelcel in op de opgegeven waarde.
Het gebruik van de aangepaste functie is eenvoudig:
=SetCellValue(target_cell, value)
waarbij target_cell een tekenreeksverwijzing is naar een cel in het werkblad (bijv. “A1”) of een uitdrukking die evalueert naar een dergelijke verwijzing. Dit omvat een uitdrukking zoals =B14 waarbij de waarde van B14 “A1” is. De functie kan in elke geldige uitdrukking worden gebruikt.
SetCellValue retourneert 1 als de waarde met succes naar de doelcel is geschreven, en 0 anders. Eventuele eerdere inhoud van de doelcel wordt overschreven.
Er zijn drie stukjes code nodig:
- de code die
SetCellValuezelf definieert - de macro die getriggerd wordt door de werkbladberekeningsgebeurtenis; en
- een utiliteitsfunctie
IsCellAddressom te verzekeren dattarget_celleen geldig celadres is.
Code voor SetCellValue Functie
Deze code moet in een standaardmodule worden geplakt die in het werkboek wordt ingevoegd. De module kan worden ingevoegd via het menu voor de Visual Basic editor, dat toegankelijk is door Visual Basic te selecteren op het tabblad Developer van het lint.
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
Worksheet_Calculate Macro Code
Deze code moet worden opgenomen in de code die specifiek is voor het werkblad waarin u SetCellValue gaat gebruiken. De eenvoudigste manier om dit te doen is met de rechtermuisknop te klikken op het tabblad van het werkblad in de Home-weergave, View Code te selecteren, en dan de code te plakken in het deelvenster editor dat verschijnt.
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 voor IsCellAddress Functie
Deze code kan in dezelfde module geplakt worden als de SetCellValue code.
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
Laten we aannemen dat u de tekst “Tekst A” wilt laten zien in cel C5 als cel B5 de waarde “groen” bevat.
Je kunt een formule gebruiken, maar omdat formules geen waarden in andere cellen kunnen veranderen, zal de formule in cel C5 moeten worden ingevoerd.
=IF(ISNUMBER(FIND("green",B5)),"Text A","")
Cel C5 zal nu alleen “Tekst A” tonen als B5 het woord “groen” bevat.
Een formule als deze kan gebouwd worden om met vele voorwaarden te werken. Je moet je eisen definiëren om hulp te krijgen bij je specifieke situatie.
Als je niet wilt dat C5 een formule heeft, kun je ook een VBA-aanpak gebruiken. Je kunt een gebeurtenis Worksheet Change uitvoeren die telkens wordt uitgevoerd als cel B5 wordt gewijzigd, hetzij door de waarde handmatig te wijzigen, hetzij door er iets in te plakken.
Een voorbeeld voor zo'n macro zou kunnen zijn
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
De condities en de plaatsing van de uitvoer zijn natuurlijk maar een voorbeeld en moeten aan jouw eisen worden aangepast.
Het verschil tussen de formule- en de macro-aanpak is
- met de formule-aanpak zal cel C5 een formule bevatten. Als de gebruiker per ongeluk de formule verwijdert, wordt de functionaliteit die de formule levert ook verwijderd. (Er zijn echter manieren om dat te beheren)
- met de VBA macro, zal cel C5 geen formule tonen, en zal de woordelijke tekst als waarde hebben, maar het veranderen van de instelling vereist kennis van Excel VBA. Ook moet met een VBA-aanpak de werkmap worden opgeslagen als een werkmap met macro’s en moet de gebruiker macro’s toestaan of het bestand tot een vertrouwd bestand maken.
Opmerking: het bovenstaande is slechts een voorbeeld. U moet uw vereisten definiëren, of u getallen of tekst wilt evalueren, of de evaluatie hoofdlettergevoelig is, wat de evaluatieregels zijn, waar u het resultaat wilt plaatsen, enz.
Om te controleren of aan een voorwaarde is voldaan, schrijft u een IF-formule in een of andere cel om “een specifieke regel tekst in een andere cel” bij te werken. De resultaatcel zal alleen de waarde van de formule bevatten, NIET de formule die die waarde genereerde, zoals dit:
Werkcel (b.v. J5) =IF(A1="yes","Specific line of text","")
Resultaat cel (b.v. B5) =J5
Dus als aan de voorwaarde is voldaan (A1=“ja”), zal B5 “Specifieke regel tekst” bevatten. Anders blijft hij leeg.
Noten: Cellen met variabele waarden bevatten gewoonlijk een of andere formule om de waarden te blijven bijwerken.
Als je niet wilt dat iemand de echte formule kent die de waarde creëert, kan de formule in een andere cel gaan om de waarde te genereren, die dan naar de resultaatcel wordt gekopieerd, bijv. B5.
Om de formule die de waarde genereert te verbergen of om de cel te verbergen waar B5 naar verwijst (om te blijven controleren of de waarde moet worden geactualiseerd), raadpleegt u: https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f
Andere functies die hetzelfde effect kunnen bereiken zijn CHOOSE, HLOOKUP, LOOKUP, VLOOKUP.
=value(cell)
Rechttoe rechtaan formule om te gebruiken, had hetzelfde probleem en het werkt.