OpenOffice.info

Code: Alles auswählen

AltesDatum = "04.12.2007" 'Beispieldatum
 
Dim Unterschied As Long
Unterschied = (Year(Date) - Year(CDate(AltesDatum))) * 12 + _
  Month(Date) - Month(CDate(AltesDatum))

Code: Alles auswählen

Rem Attribute VBA_ModuleType=VBADocumentModule
Option VBASupport 1
Private Sub GO_Click()
 Dim R As Integer
 Dim FIR As Integer
 Dim ANF As Integer
 Dim POS As Integer
 Dim NEG As Integer
 Dim ANT As Integer
 Dim KEI As Integer
 Dim DAT As String
 Dim TEX As String
 Dim TEM As Integer
 
 R = 6
 FIR = 0
 ANF = 0
 ANT = 0
 POS = 0
 NEG = 0
 KEI = 0
 DAT = Date
 TEX = Date
 TEM = 0
 
 While Cells(R, 4).Value <> ""
    If Cells(R, 3).Value = "" Then
                                Cells(R, 6).Value = "XXXXX"
                                Cells(R, 6).Interior.ColorIndex = 2
    Else
                                If Cells(R, 5).Value = "" Then
                                                          Cells(R, 6).Value = "Waiting"
                                                          Cells(R, 6).Interior.ColorIndex = 6
                                Else
                                                           If Cells(R, 5).Value = "OK" Then
                                                                                       Cells(R, 6).Value = "OK"
                                                                                       Cells(R, 6).Interior.ColorIndex = 4
                                                                                       POS = POS + 1
                                                                                       ANT = ANT + 1
                                                                                       If Cells(R, 5).Value <> "" And Cells(R, 7).Value = "" Then
                                                                                                                                             Cells(R, 7).Value = Date
                                                                                       End If
                                                           Else
                                                                                       Cells(R, 6).Value = "KO"
                                                                                       Cells(R, 6).Interior.ColorIndex = 3
                                                                                       NEG = NEG + 1
                                                                                       ANT = ANT + 1
                                                                                       If Cells(R, 5).Value <> "" And Cells(R, 7).Value = "" Then
                                                                                                                                             Cells(R, 7).Value = Date
                                                                                       End If
                                                           End If
                                End If
    End If
    If Cells(R, 3).Value <> "" Then
                               ANF = ANF + 1
    End If
    
    FIR = FIR + 1
  
  DAT = CStr(Date)
  TEX = CStr(Cells(R, 3).Value)
  
  MsgBox("R: " & R & Chr(13) & "DAT: " & DAT & Chr(13) & "TEX: " & TEX & Chr(13) & "Firmen: " & FIR & Chr(13) & "Anfragen: " & ANF & Chr(13) & "Antworten: " & ANT & Chr(13) & "Ignored: " & KEI & Chr(13) & "Positiv: " & POS & Chr(13) & "Negativ: " & NEG)
  
  TEM = CInt(Mid(DAT, 4, 2)) - CInt(Mid(TEX, 4, 2))
  If ( (Cells(R, 5).Value = "") AND (TEM >= 3) AND (Cells(R, 7).Value <> "XXXXX") ) Then
  	KEI = KEI + 1
  	Cells(R, 6).Value = "IGNORED"
  	Cells(R, 6).Interior.ColorIndex = 9
  	Cells(R, 7).Value = "XXXXX"
  End If
  R = R + 1
  Wend  
  
  Cells(7, 10).Value = FIR
  Cells(9, 10).Value = ANF
  Cells(12, 10).Value = POS
  Cells(13, 10).Value = NEG
  Cells(14, 10).Value = (ANF - KEI - POS - NEG)
  Cells(15, 10).Value = KEI
  Cells(11, 10).Value = ANT
End Sub

Code: Alles auswählen

...
TEX = CStr(Cells(R, 3).formulalocal)
....

Code: Alles auswählen

  MsgBox("R: " & R & Chr(13) & "DAT: " & DAT & Chr(13) & "TEX: " & TEX)
  
  DAT = CStr(Date)
  TEX = CStr(Cells(R, 3).Value)
  
  MsgBox("R: " & R & Chr(13) & "DAT: " & DAT & Chr(13) & "TEX: " & TEX)
  
  TEM = CInt((Mid(DAT, 4, 2)) - CInt(Mid(TEX, 4, 2)))
  
  If ( TEM >= 3 ) Then