Frage Microsoft Microsoft Office

gelöst Wert in Zelle abhängig von eingegebenen Werten

Option Explicit 

Option Compare Text 

Const Start = 5     'Spalte E 

Const Count = 3     'Zähler Spalten (E, H,...) 

Const Werte = "[atibn]" 

Function TestDay() As Integer 

    Dim Line As Integer, i As Integer 

    Application.Volatile:  Line = Application.Caller.Row 

    For i = Start To 23 * Count + Start Step Count 

        If Cells(Line, i) Like Werte Then TestDay = 1: Exit For 

    Next 

End Function

=WENN(ODER(ZÄHLENWENN(E3:BV3;"a");ZÄHLENWENN(E3:BV3;"b");ZÄHLENWENN(E3:BV3;"i");ZÄHLENWENN(E3:BV3;"n");ZÄHLENWENN(E3:BV3;"t"));1;0)

Option Explicit 

Option Compare Text 

Const RangeOK = "A35"       'Test Monatsblatt: Zelle.Adresse 

Const SheetOK = "*Monat*"   'Test Monatsblatt: Zelle.Inhalt z.B. "Monat" oder "Text Monat Text" 

Const RowPos1 = 3           'Zeile 3 

Const RowEnde = 33          'Zeile 33 

Const ColPos1 = 5           'Spalte E 

Const ColEnde = 74          'Spalte BV 

Const ColWert = 77          'Spalte BY 

Const Counter = 3           'Zähler Spalten (E, H,...) 

Const Zeichen = "[atibn]"   'Gültige Zeichen Groß/Klein 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) 

    Dim Row As Integer, Col As Integer, Result As Integer, i As Integer 

    If Not Sh.Range(RangeOK) Like SheetOK Then Exit Sub 

    Row = Source.Row:  Col = Source.Column 

    If Col < ColPos1 Or Col > ColEnde Or Row < RowPos1 Or Row > RowEnde Then Exit Sub 

    For Row = RowPos1 To RowEnde 'Alle aktualisieren, sonst Bug wenn mehrere Zellen markiert sind 

        Result = 0 

        For i = ColPos1 To ColEnde Step Counter 

            If Cells(Row, i) Like Zeichen Then Result = 1: Exit For 

        Next 

        Cells(Row, ColWert) = Result 

    Next 

End Sub