joes auftragsreport 20180622

Function ColToLetter(col As Long) As String
    ColToLetter = Split(Cells(1, col).Address, "$")(1)
End Function

Sub test_3()


    Dim matching_sheetName As String
    matching_sheetName = "Matching"
    Dim Auftrags_sheetName As String
    Auftrags_sheetName = "Auftragseingang"

' ActiveWorkbook.Sheets(matching_sheetName).Range("c3:zz999999").Clear

    '   need to wor this over
    If StrComp(ActiveWorkbook.Sheets(1).Name, Auftrags_sheetName, vbTextCompare) = 0 And _
       StrComp(ActiveWorkbook.Sheets(2).Name, matching_sheetName, vbTextCompare) = 0 Then
         Set sn = Worksheets(matching_sheetName)
         sn.Range("c3:az99").Clear
    Else
         ' MsgBox "Raus hier"
         Exit Sub
    End If

Dim myFile As String, rng As Range, cell As Range, cellValue As Variant, i As Integer, j As Integer

' myFile = "\\Poel5-Daten\Altona-Büro-Dateien\11 Büroteam\Joe Koenig\Excel Übungen\out.txt"
' Open myFile For Output As #1


' Set KD_rng = Worksheets(matching_sheetName).Range("B1:AL1")
Set KD_rng = Worksheets(matching_sheetName).Range("=Kunden")
' Set MA_rng = Worksheets(matching_sheetName).Range("A2:A22")
Set MA_rng = Worksheets(matching_sheetName).Range("=Mitarbeiter")

For Each KD_cell In KD_rng
    For Each MA_cell In MA_rng
        counter = Application.WorksheetFunction.CountIfs(Worksheets(Auftrags_sheetName).Range("e2:e999"), KD_cell, Worksheets(Auftrags_sheetName).Range("f2:f999"), MA_cell)
        counter = counter + Application.WorksheetFunction.CountIfs(Worksheets(Auftrags_sheetName).Range("e2:e999"), KD_cell, Worksheets(Auftrags_sheetName).Range("g2:g999"), MA_cell)
        If counter > 0 Then
            Worksheets(matching_sheetName).Range(ColToLetter(KD_cell.Column) & MA_cell.Row).Value = counter
        Else
            Worksheets(matching_sheetName).Range(ColToLetter(KD_cell.Column) & MA_cell.Row).Clear
        End If
        ' Write #1, counter
    Next MA_cell
Next KD_cell


' Write #1, "Hello World   " & Now



Close #1

' foo = Shell("notepad.exe \\Poel5-Daten\Altona-Büro-Dateien\11 Büroteam\Joe Koenig\Excel Übungen\out.txt", 1)

End Sub