AW: Tabelle transponieren
29.08.2016 13:29:04
UweD
Hallo
per Makro....
Sub Trans()
Dim TB, LR&, LC%, i%, j&, z&
Set TB = Sheets("Soll")
With Sheets("Ist")
LR = .Cells(.Rows.Count, 1).End(xlUp).Row 'letzte Zeile der Spalte A
LC = .Cells(1, .Columns.Count).End(xlToLeft).Column 'letzte Spalte von Zeile 1
TB.Cells.ClearContents
TB.Cells(1, 1) = "Standort"
TB.Cells(1, 2) = "Datum"
TB.Cells(1, 3) = "Umsatz"
z = 1
For i = 2 To LC
For j = 2 To LR
z = z + 1
TB.Cells(z, 1) = .Cells(j, 1)
TB.Cells(z, 2) = .Cells(1, i)
Next j
Next i
TB.Range("C2:C" & z).Formula = _
"=SUMPRODUCT((Ist!R2C1:R" & LR & "C1=Soll!RC1)*(Ist!R1C2:R1C" & _
LC & "=Soll!RC2)*(Ist!R2C2:R" & LR & "C" & LC & "))"
TB.Range("C2:C" & z).Value = TB.Range("C2:C" & z).Value
End With
End Sub
VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media
Code erstellt und getestet in Office 15 - mit VBAHTML 12.6.0
Gruß UweD