IsEmpty() und Ampel-Regel

Guten Morgen,
ich versuche momentan mithilfe von IsEmpty den Wert einer Zelle zu überprüfen und daraufhin eine Zahl in eine andere Zelle zu schreiben, welche mit der Ampel-Regel belegt ist.
Kurze Erläuterung der Ampelwerte:
B5 & C5 leer macht grün
B5 = 0 & C5 = nicht leer macht rot
B5 = 1 & C5 = nicht leer macht gelb
alles andere soll -1 ergeben, somit schwarz
Dasselbe gilt für B6 und C6.
Hier der Code:


Sub Ampel()
Dim B5b As Boolean, B6b As Boolean, C5b As Boolean, C6b As Boolean, B5 As Integer, B6 As  _
Integer, C5 As String, C6 As String
'B5b = Worksheets("Template").Range("B5")
'B6b = Worksheets("Template").Range("B6")
B5 = Worksheets("Template").Range("B5") '.Value
B6 = Worksheets("Template").Range("B6") '.Value
'C5b = Worksheets("Template").Range("C5")
'C6b = Worksheets("Template").Range("C6")
C5 = Worksheets("Template").Range("C5")
C6 = Worksheets("Template").Range("C6")
If IsEmpty(B5) = True And IsEmpty(C5) = True Or IsEmpty(B6) = True And IsEmpty(C6) = True Then
Worksheets("Template").Range("D4").Value = 2 'green light
ElseIf B5 = 0 And IsEmpty(C5) = False Or B6 = 0 And IsEmpty(C6) = False Then
Worksheets("Template").Range("D4").Value = 0 'red light
ElseIf B5 = 1 And IsEmpty(C5) = False Or B6 = 1 And IsEmpty(C6) = False Then
Worksheets("Template").Range("D4").Value = 1 'yellow light
Else
Worksheets("Template").Range("D4").Value = -1 'black light
End If
End Sub

Egal welche Werte ich in Excel eingebe, am Ende wird D4 = 0 gesetzt.
Boolean, Integer, String, bringt mich ein bisschen durcheinander. Muss ich dafür - wie schon im Code angedeutet - neue Variablen definieren?
Danke für eure Hilfe!
Frederik

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub Ampel1() Dim B5 As Variant, B6 As Variant, C5 As Variant, C6 As Variant With Worksheets("Template") B5 = .Range("B5") B6 = .Range("B6") C5 = .Range("C5") C6 = .Range("C6") If ((IsEmpty(B5) = True And Not B5 = 0) And IsEmpty(C5) = True) Or ((IsEmpty(B6) = True _ And Not B6 = 0) And IsEmpty(C6) = True) Then .Range("D4").Value = 2 'green light ElseIf (B5 = "0" And IsEmpty(C5) = False) Or (B6 = "0" And IsEmpty(C6) = False) Then .Range("D4").Value = 0 'red light ElseIf (B5 = "1" And IsEmpty(C5) = False) Or (B6 = "1" And IsEmpty(C6) = False) Then .Range("D4").Value = 1 'yellow light Else .Range("D4").Value = -1 'black light End If End With 'Debug.Print B5, C5, B6, C6 End Sub

Hallo,
so?

Public Sub Ampel()

    Dim B5 As Variant, B6 As Variant, C5 As Variant, C6 As Variant
    Dim lngIndex As Long

    With Worksheets("Template")

        For lngIndex = 5 To 17 Step 3

            B5 = .Cells(lngIndex, 2).Value
            B6 = .Cells(lngIndex + 1, 2).Value
            C5 = .Cells(lngIndex, 3).Value
            C6 = .Cells(lngIndex + 1, 3).Value

            If IsEmpty(B5) And IsEmpty(C5) Or IsEmpty(B6) And IsEmpty(C6) Then
                .Cells(lngIndex - 1, 4).Value = 2 'green light
            ElseIf B5 = 0 And Not IsEmpty(C5) Or B6 = 0 And Not IsEmpty(C6) Then
                .Cells(lngIndex - 1, 4).Value = 0 'red light
            ElseIf B5 = 1 And Not IsEmpty(C5) Or B6 = 1 And Not IsEmpty(C6) Then
                .Cells(lngIndex - 1, 4).Value = 1 'yellow light
            Else
                .Cells(lngIndex - 1, 4).Value = -1 'black light
            End If
        Next
    End With
End Sub

Gruß
Nepumuk

Public Sub Ampel() Dim B5 As Variant, B6 As Variant, C5 As Variant, C6 As Variant Dim lngIndex As Long With Worksheets("Template") For lngIndex = 5 To 17 Step 3 B5 = .Cells(lngIndex, 2).Value B6 = .Cells(lngIndex + 1, 2).Value C5 = .Cells(lngIndex, 3).Value C6 = .Cells(lngIndex + 1, 3).Value If ((IsEmpty(B5) = True And Not B5 = 0) And IsEmpty(C5) = True) Or ((IsEmpty(B6) = _ True And Not B6 = 0) And IsEmpty(C6) = True) Then .Cells(lngIndex - 1, 4).Value = 2 'green light ElseIf (B5 = "0" And IsEmpty(C5) = False) Or (B6 = "0" And IsEmpty(C6) = False) _ Then .Cells(lngIndex - 1, 4).Value = 0 'red light ElseIf (B5 = "1" And IsEmpty(C5) = False) Or (B6 = "1" And IsEmpty(C6) = False) _ Then .Cells(lngIndex - 1, 4).Value = 1 'yellow light ElseIf (B5 = "2" And IsEmpty(C5) = False) Or (B6 = "2" And IsEmpty(C6) = False) _ Then .Cells(lngIndex - 1, 4).Value = 2 'yellow light Else .Cells(lngIndex - 1, 4).Value = -1 'black light End If Next End With End Sub

Beliebteste Forumthreads (12 Monate)

14 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread