Wieder einmal Excel

[jak]

Bei der Beschreibung kann ich mir das schon Vorstellen ...

Also kurzgefaßt:

Du hast Kunden und Mitarbeiter und willst die Summe aller Stunden pro Kunde?

Ich würde das mit VBA machen:

'Function lookForName
'Determines if a customer's name is already found in the
'second (control) sheet; returns the row number if it is found
'else the negative number of the last row
'The Function terminates if it finds two empty lines
Function lookForName(cName As String) As Integer
  Dim colNum, rowNum As Integer
  Dim custName As String
  colNum = 1
  rowNum = 1
  Do
    custName = Worksheets("Kontrolle").Cells(rowNum, colNum).Value
    If (custName = cName) Then
      lookForName = rowNum
      Exit Function
    End If
    rowNum = rowNum + 1
    'the next line is to be sure that the function only terminates after
    'two empty lines
    If (custName = "") Then custName = Worksheets("Kontrolle").Cells(rowNum, colNum).Value
  Loop Until custName = ""
  lookForName = (rowNum - 1) * -1
End Function


Sub Makro1()
  Dim name, temp As String 'name of the customer, temp
  Dim wTime As Single 'The worktime
  Dim i, j, targetRow As Integer 'count variables
  Dim colStart, rowStart, nextCol, timeCol, checkCol As Integer
  colStart = 3 'start value for colums (=C)
  rowStart = 2 'start value for rows (=2)
  nextCol = 3 'the distance to the next column
  timeCol = -1 'the relative position of the
    'Column where the time is stored
  checkCol = 1 'The relative position of the column
    ' in which to check if a Column counts
    ' 1 or "true" means count, everything else means don't count
  i = colStart
  j = rowStart
  Do
    Do
      name = Worksheets("Dienstplan").Cells(j, i).Value
      temp = Worksheets("Dienstplan").Cells(j, i + checkCol).Value
      If ((temp = "1") Or (temp = "true")) Then
        wTime = Worksheets("Dienstplan").Cells(j, i + timeCol).Value
        targetRow = lookForName((name))
        If (targetRow > 0) Then 'Name already exists
          Dim c As Object
          Set c = Worksheets("Kontrolle").Cells(targetRow, 2)
          c.Value = c.Value + wTime
        Else 'Name not found--> create new cells
          targetRow = targetRow * -1
          Worksheets("Kontrolle").Cells(targetRow, 1).Value = name
          Worksheets("Kontrolle").Cells(targetRow, 2).Value = wTime
        End If
      End If
      j = j + 1
      If (name = "") Then name = Worksheets("Dienstplan").Cells(j, i).Value
    Loop Until (name = "")
    i = i + nextCol
    j = rowStart
    name = Worksheets("Dienstplan").Cells(j, i).Value
  Loop Until (name = "")

End Sub

Der Code ist sicher nicht die effizienteste Art es zu lösen, aber es ist zumindest mal eine Lösung

Jak