Więc w zasadzie chcę napisać formułę, która sprawdzi warunek, a jeśli ten warunek jest spełniony, to chcę wkleić określoną linię tekstu w innej komórce. Powinienem zauważyć, że nie chcę, aby formuła istniała w komórce, do której chcę wkleić wartość. Więc na przykład, jeśli chcę wkleić wartość do komórki B5, nie chcę, aby formuła znajdowała się w komórce B5…
Poniższe podejście wykorzystuje obejście opisane tutaj i tutaj , aby umożliwić funkcji arkusza zdefiniowanej w VBA ustawienie wartości innej komórki.
Funkcja niestandardowa przechowuje w zmiennych globalnych adres komórki docelowej oraz wartość, na jaką ta komórka ma być ustawiona. Następnie makro, które jest uruchamiane podczas przeliczania arkusza, odczytuje zmienne globalne i ustawia komórkę docelową na określoną wartość.
Użycie funkcji niestandardowej jest proste:
=SetCellValue(target_cell, value)
gdzie target_cell jest łańcuchowym odniesieniem do komórki w arkuszu (np. “A1”) lub wyrażeniem, które ocenia się na takie odniesienie. Obejmuje to wyrażenie takie jak =B14, gdzie wartością B14 jest “A1”. Funkcję można zastosować w dowolnym prawidłowym wyrażeniu.
SetCellValue zwraca 1, jeżeli wartość została pomyślnie zapisana do komórki docelowej, a 0 w przeciwnym przypadku. Wszelkie poprzednie zawartości komórki docelowej są nadpisywane.
Potrzebne są trzy fragmenty kodu:
SetCellValue IsCellAddress zapewniająca, że target_cell jest prawidłowym adresem komórki. Kod ten należy wkleić do standardowego modułu wstawianego do skoroszytu. Moduł można wstawić za pomocą menu edytora Visual Basic, do którego wchodzi się, wybierając Visual Basic z zakładki Developer wstążki.
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
Kod ten musi znaleźć się w kodzie właściwym dla arkusza, w którym będziemy używać SetCellValue. Najprostszym sposobem jest kliknięcie prawym przyciskiem myszy na zakładce arkusza w widoku Home, wybranie opcji View Code, a następnie wklejenie kodu w okienku edytora, które się pojawi.
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
Ten kod można wkleić do tego samego modułu co kod 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
Załóżmy, że chcesz, aby tekst “Tekst A” był wyświetlany w komórce C5, jeśli komórka B5 zawiera wartość “zielony”.
Możesz możesz użyć formuły, ale ponieważ formuły nie mogą zmieniać wartości w innych komórkach, formuła będzie musiała zostać wpisana do komórki C5.
=IF(ISNUMBER(FIND("green",B5)),"Text A","")
Komórka C5 będzie teraz wyświetlać “Tekst A” tylko wtedy, gdy komórka B5 zawiera słowo “zielony”.
Formułę taką jak ta można zbudować tak, aby działała z wieloma warunkami. Musisz zdefiniować swoje wymagania, aby uzyskać pomoc w konkretnej sytuacji.
Jeśli nie chcesz, aby C5 posiadał formułę, możesz również użyć podejścia VBA. Możesz uruchomić zdarzenie Worksheet Change, które będzie uruchamiane za każdym razem, gdy komórka B5 zostanie zmieniona, albo przez ręczną edycję wartości, albo przez wklejenie czegoś do niej.
Przykładem takiego makra może być
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
Warunki i umiejscowienie wyjścia są oczywiście tylko przykładem i muszą być dostosowane do Twoich wymagań.
Różnica między podejściem opartym na formule a podejściem opartym na makrze jest następująca
Uwaga: powyższe jest tylko przykładem. Musisz zdefiniować swoje wymagania, czy oceniać liczby czy tekst, czy ocena jest wrażliwa na wielkość liter, jakie są reguły oceny, gdzie umieścić wynik itp.
Aby sprawdzić, czy warunek jest spełniony, napisz formułę JEŻELI w jakiejś komórce, aby zaktualizować “określoną linię tekstu w innej komórce”. Komórka wynikowa będzie zawierała tylko wartość formuły, a NIE formułę, która wygenerowała tę wartość, np:
Komórka robocza (np. J5) =IF(A1="yes","Specific line of text","")
Komórka wynikowa (np. B5) =J5
Czyli jeśli warunek jest spełniony (A1=“tak”), to B5 będzie zawierała “Konkretną linijkę tekstu”. W przeciwnym razie pozostanie pusta.
Uwagi: Komórki, które mają zmienne wartości, zazwyczaj zawierają jakąś formułę, która aktualizuje ich wartości.
Jeśli nie chcesz, aby ktokolwiek znał prawdziwą formułę tworzącą wartość, formuła może zostać umieszczona w innej komórce, aby wygenerować wartość, która następnie zostanie skopiowana do komórki wynikowej, np. B5.
Aby ukryć formułę, która wygenerowała wartość lub ukryć komórkę, do której odnosi się B5 (aby ciągle sprawdzać, czy wartość wymaga aktualizacji), odwołaj się do: https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f
Inne funkcje, które mogą osiągnąć ten sam efekt to CHOOSE, HLOOKUP, LOOKUP, VLOOKUP.
=value(cell)
Prosta formuła do użycia, miałem ten sam problem i działa.