Makros und anderes - Excel

Dieses Thema Makros und anderes - Excel im Forum "Microsoft Office Suite" wurde erstellt von Balu66, 15. März 2013.

Thema: Makros und anderes - Excel Hallo zusammen, da ich in diesem Forum schon häufiger sehr gute Hilfe erhalten habe, versuche ich es heute auch mal...

  1. Hallo zusammen,

    da ich in diesem Forum schon häufiger sehr gute Hilfe erhalten habe, versuche ich es heute auch mal wieder.

    Ich habe aus einem Export eines Programmes folgende Excel-Tabelle:
    http://www.minze-d.de/tp.xls

    Diese Daten sollen nach meinen Vorenstellungen bearbeitet werden. Ich habe daher ein Tabelle, die schon mit Makros gearbeitet, genommen, und versucht, die auf die speziellen Bedürfnisse umzustellen.
    Natürlich bin ich wieder gescheitert... sonst wäre ich nicht wieder hier. Hier die andere Datei:
    http://www.minze-d.de/E8.xlsm
    Diese Datei arbeitet mit der tp.xls zusammen, und es wird erwartet, dass diese auf C:\ liegt.

    Im Reiter Import hole ich mir relevante Daten aus der tp.xls und formatiere diese dort auf benötigte Datums- und andere Formate. Dort sind auch ein paar Rechenfelder hinterlegt, um Daten zu errechnen, die ich in dieser Form nicht aus dem anderen Programm exportieren kann. Aus den Spalten A-J drucke ich mir dann eine benötigte Liste.

    Im Reiter Formular hole ich mir dann die Felder MwSt-Satz, Nettobetrag, Steuerbetrag und den Bruttobetrag. Mehr ist hier nicht benötigt.

    Im Reiter Druck möchte ich gern Summenfelder haben. Dort ist jetzt diese alte Makro hinterlegt, was ich nicht umgebastelt bekommen habe. Dort solen alle Werte, die in Spalte A (hier) die Werte 0, 7 oder 19 haben, als Summe angezeigt werden, und alle Zeilen, die in Spalte B oder D den Wert 0 haben, ausgeblendet werden.

    Den Reiter Uebernahme habe ich, auch wenn er hier quasi doppelt auftaucht, aus einer anderen Liste übernommen, damit ich nicht versehentlich benötigte, mir nicht sichtbar hinterlegte Formeln oder Makros lösche.

    Im Reiter Auswertung waren in der alten Datei die Summenwerte hinterlegt. Auch hier habe ich versucht, die Werte anzupassen, aber ich bekomme dort einfach nicht die Summen hinein, die an anderer Stelle gebraucht werden. :-(

    Konnte ich so ungefähr verständlich erlautern, was ich benötige?
    Kann mir jemand helfen?

    Bis denne
    Wolfgang
     
  2. Hallo Wolfgang,

    Gibt es auch die Kombinationen ? :
    Wert 0,7 oder 19 in A, Spalte B Wert=0, Spalte D Wert#0
    Wert 0,7 oder 19 in A, Spalte B Wert#0, Spalte D Wert=0

    Wenn ja, sollen diese ausgeblendeten Werte dann in der Summe enthalten sein ?

    Gruß Matjes :)
     
  3. Wenn Spalte B=0 (netto), dann muss Spalte D (brutto) auch =0 sein. Wenn es nicht so wäre, hätte ich einen Fehler mit den Werten, die ich mir aus einem Abwicklungsprogramm hole. (Wäre sehr böse). :)

    In einem solchen Fall wäre es schön, wenn eine Fehlermeldung käme. :)
    (Es ist aber nur zu 0,0001% damit zu rechnen)
     
  4. Hallo Wolfgang,

    dann tausch mal das Makro im Blatt Druck gegen das folgende aus und schau, ob es so i.O. ist.

    Gruß Matjes :)
    Code:
    Private Sub Worksheet_Activate()
     Dim z As Long, lAnf As Long, lEnd As Long, sp As Long
     Dim strA As String, strX As String
     
     On Error GoTo Fehler
     Application.ScreenUpdating = False-> BildschirmAktualisierung AUS
     
     Cells.EntireRow.Hidden = False-> Zuerst alle Zeilen einblenden
     lAnf = 2
     lEnd = Range(A65536).End(xlUp).Row->letzte belegte Zeile aus Spalte A bestimmen
    ->Zeilen ausblenden mit Spalte B=0 enthalten
     For z = lAnf To lEnd-> Bis letzte Belegte Zelle in Spalte A Prüfen
      If Cells(z, 2) = 0 Then
      ->prüfen, ob auch in Spalte D=0
       If Cells(z, 4) = 0 Then
       ->Zeile ausblenden
        Rows(z).EntireRow.Hidden = True
       Else
       ->Fehler Spalte D#0
        MsgBox (In Zeile  & z &  : Spalte B=0, Spalte D#0)
        Error 13->Error setzen
       End If
      Else
       If Cells(z, 4) = 0 Then
       ->Fehler Spalte B#0
        MsgBox (In Zeile  & z &  : Spalte B#0, Spalte D=0)
        Error 13->Error setzen
       End If
      End If
     Next
     
    ->Summenformel in Zeile nach lEnd in Spalte B,C,D setzen
    ->Summierung erfolgt für Zeilen, die in Spalte A 0, 7, 19 enthalten
    ->Format jeweils aus Zeile lAnf übernehmen
     For sp = 2 To 4
     ->Format übernehemen
      Range(Cells(lAnf, sp), Cells(lAnf, sp)).Select
      Selection.Copy
      Range(Cells(lEnd + 1, sp), Cells(lEnd + 1, sp)).Select
      Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False
     ->Summenformel setzen
      strA = Range(Cells(lAnf, 1), Cells(lEnd, 1)).Address(RowAbsolute:=False, ColumnAbsolute:=False)
      strX = Range(Cells(lAnf, sp), Cells(lEnd, sp)).Address(RowAbsolute:=False, ColumnAbsolute:=False)
      Cells(lEnd + 1, sp).Formula = _
      =SUMPRODUCT(( & strX & )*( & strA & =0)+( & strX & )*( & strA & =7)+( & strX & )*( & strA & =19))
     Next
     Application.ScreenUpdating = True-> BildschirmAktualisierung EIN
     Exit Sub
    Fehler:
     Application.ScreenUpdating = True-> BildschirmAktualisierung EIN
     MsgBox Es ist ein Fehler aufgetreten!
    End Sub
    bzgl
    müßtest du näher beschreiben, was in den einzelnen Zellen stehen soll
     
  5. Hallo Wolfgang,

    ich hab das Makro für das Blatt Druck nochmal bzgl. Perfomance überarbeitet. Es blendet jetzt aufeinanderfolgende auszublendende Zeilen nicht mehr einzeln sondern mit einem Befehl aus.

    Gruß Matjes :)
    Code:
    Private Sub Worksheet_Activate()
     Dim z As Long, lAnf As Long, lEnd As Long, sp As Long
     Dim x As Long, zAnf As Long, zEnd As Long
     Dim strA As String, strX As String
     Dim AuszublendendeZeilen() As Long
     
     On Error GoTo Fehler
     Application.ScreenUpdating = False-> BildschirmAktualisierung AUS
     
     Cells.EntireRow.Hidden = False-> Zuerst alle Zeilen einblenden
     lAnf = 2
     lEnd = Range(A65536).End(xlUp).Row->letzte belegte Zeile aus Spalte A bestimmen
     ReDim AuszublendendeZeilen(lAnf To lEnd)
    ->Zeilen ausblenden mit Spalte B=0 enthalten
     For z = lAnf To lEnd-> Bis letzte Belegte Zelle in Spalte A Prüfen
      If Cells(z, 2) = 0 Then
      ->prüfen, ob auch in Spalte D=0
       If Cells(z, 4) = 0 Then
       ->Kennzeichen Zeile ausblenden setzen
        AuszublendendeZeilen(z) = 1
       Else
       ->Fehler Spalte D#0
        MsgBox (In Zeile  & z &  : Spalte B=0, Spalte D#0)
        Error 13->Error setzen
       End If
      Else
       If Cells(z, 4) = 0 Then
       ->Fehler Spalte B#0
        MsgBox (In Zeile  & z &  : Spalte B#0, Spalte D=0)
        Error 13->Error setzen
       End If
      End If
     Next
    ->aufeinander folgende auszublendende Zeilen ausblenden
     For z = lAnf To lEnd
      If AuszublendendeZeilen(z) = 1 Then
       zAnf = z
       zEnd = z
       For x = z To lEnd - 1
        If AuszublendendeZeilen(x + 1) = 1 Then
         zEnd = x + 1
        Else
         Exit For
        End If
       Next
      ->Zeilengruppe ausblenden
       Rows(zAnf & : & zEnd).EntireRow.Hidden = True
      ->Laufindex auf zuletzte ausgeblendete Zeile setzen
       z = zEnd
      End If
     Next
    ->Summenformel in Zeile nach lEnd in Spalte B,C,D setzen
    ->Summierung erfolgt für Zeilen, die in Spalte A 0, 7, 19 enthalten
    ->Format jeweils aus Zeile lAnf übernehmen
     For sp = 2 To 4
     ->Format übernehemen
      Range(Cells(lAnf, sp), Cells(lAnf, sp)).Select
      Selection.Copy
      Range(Cells(lEnd + 1, sp), Cells(lEnd + 1, sp)).Select
      Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False
     ->Summenformel setzen
      strA = Range(Cells(lAnf, 1), Cells(lEnd, 1)).Address(RowAbsolute:=False, ColumnAbsolute:=False)
      strX = Range(Cells(lAnf, sp), Cells(lEnd, sp)).Address(RowAbsolute:=False, ColumnAbsolute:=False)
      Cells(lEnd + 1, sp).Formula = _
      =SUMPRODUCT(( & strX & )*( & strA & =0)+( & strX & )*( & strA & =7)+( & strX & )*( & strA & =19))
     Next
     
     Application.ScreenUpdating = True-> BildschirmAktualisierung EIN
     Exit Sub
    Fehler:
     Application.ScreenUpdating = True-> BildschirmAktualisierung EIN
     MsgBox Es ist ein Fehler aufgetreten!
    End Sub
    
     
  6. Hallo Wolfgang,

    wenn deine Auswertung sich auf das Blatt Uebernahme beziehen soll müßtest du 4 Namen definieren.

    auf Blatt Uebernahme A3:A500 markieren
    Formeln->Namen defienieren-> MWST

    auf Blatt Uebernahme B3:B500 markieren
    Formeln->Namen defienieren-> netto

    auf Blatt Uebernahme C3:C500 markieren
    Formeln->Namen defienieren-> Steuer

    auf Blatt Uebernahme D3:D500 markieren
    Formeln->Namen defienieren-> brutto


    Gruß Matjes :)
     
  7. Hallo Matjes,

    vielen Dank für Deine Unterstützung. Ich habe das nach Deinen Vorgaben eingebaut und die Ergebnisse sehen sehr gut aus.

    Druck und Uebernahme zeigen jetzt sehr ähnliche Werte, wie ich sie in dieser Form gar nicht (mehrfach) benötige, aber das ist/wäre eine rein kosmetische Korrektur. Löschen werde ich da nichts, damit nicht irgendwelche in der Tiefe benötigten Dinge im Nirwana verschwinden. :)

    Benötigt werden neben dem Import der Reiter Formular, wo ich aus den Spalten A-J eine Umsatzliste drucke, und der Reiter Auswertung als Summenblatt zum Buchen für den Steuerberater.
    Wenn da also einfach was weg kann, wäre eine Info schön.

    Es befindet sich (aber), bedingt durch die importierten Daten, ein kleiner Fehler in der/den Auswertungen (nicht der Reiter, sondern im Sinne von den Ergebnissen, die ich erhalte).

    Das Abwicklungsprogramm hat keine Datenbankfelder, in denen die Umsätze nach MwSt. getrennt hinterlegt sind. Aus diesem Grunde hole ich mir zu jeder Import (Rechnungen) jede einzelne Position der Rechnung in den Import. Das ist an sich auch noch nicht das Problem, ABER:
    die MwSt. der Versandkosten wird daran ermittelt, welcher Netto-Warenwert höher ist. Bedeutet: ist der Warenwert der Artikel mit 7% höher, dann bekommt der Versand steuertechnisch die 7% zugeordnet, und wenn es der Warenwert der Artikel mit 19% ist, enthält der Verand steuertechnisch 19% MwSt.

    Den Steuersatz des Versands bekomme ich über die angezapfte Datenbank. Im Reiter Import befinden sich in den Spalten K und L die MwSt.-Sätze zu Ware und zum Versand. In Spalte N vergleiche ich diese Werte, und wenn Ware und Versand den gleichen Satz haben, dann wird der Versand importiert, und wenn sie nicht identisch (also zugehörig sind), wird der Wert 0,00 eingesetzt.

    Das klappt so lange, wie ich unter gleicher Rechnungsnummer (Spalte A) nicht mehrere Positionen mit gleichem Steuersatz habe, weil dann mit meiner einfachen Formel die Versandkosten doppelt eingesetzt und für die Berechnung herangezogen werden. Beispeilhaft sieht man das in den Zeilen 8-11.

    Kann man abfangen, dass in Abhängigkeit von Spalte A (identische Rechnungsnummer) verhindert, bzw. dafür gesorgt wird, dass nur 1x maximal pro Rechnungsnummer die Versandkosten (Spalte O) eingesetzt werden?

    Unter die Abteilung Kosmetik würde folgendes fallen:
    Also Umsatzliste brauche ich pro Rechnungsnummer (Bereich Formular) eigentlich nur einen Summenwert je MwSt.: im konkreten Beispiel könnten die Zeilen 8+11 und 9+10 summiert werden. Das würde die Liste kürzer und übersichtlicher machen. Da wäre auf der Aufwand in Relation zum Nutzen zu sehen: wäre schön, aber nicht zwingend erforderlich. Ich weiss nicht, wie leicht oder schwer das mit Excel darzustellen ist.

    Ich habe jetzt die Dateien mit den aktuellen Änderungen hochgeladen (unter gleicher Verlinkung).

    Vielen Dank schon für die Hilfe, die mich doch einen entscheidenden Schritt nach vorne gebracht hat.

    Bis denne
    Wolfgang
     
  8. Hallo Wolfgang,

    kann man die Import-Datei (tp.xls) nicht so vorverarbeiten (per Makro), daß deine Probleme gelöst werden ?

    a) Zusammenfassung von Zeilen mit gleichen Kombination von Kundennummer/MWstSatz
    b) Bearbeitung bzgl Versandkosten pro Kundennummer
    c) ...

    Gruß Matjes :)
     
  9. Hallo Matjes,

    vielen Dank für Deine erneut sehr schnelle Antwort.

    Das mag gehen, aber ich bin und bleibe wohl leider ein User, der es schafft, ein paar Formeln zu basteln, hier und da mit Hilfe des Internets ein paar Funktionen einzubauen, aber da sind und werden die elementarsten Dinge bleiben. Leider.

    Der von Dir angezeigte Weg verschliesst sich mir daher leider etwas.

    In meinem Abwicklungsprogramm kann ich mir anhand der Schnittstelle Felder definieren, die ich exportieren will: dort kann ich gerade mal was zum Währungsformat hinterlegen. Das ist sehr rudimentär. Die Export-Datei kann ich entweder direkt in Excel öfnnen oder als CSV downloaden.

    Um den direkten Weg zu nehmen, habe ich mich für direkt in Excel öffnen entschieden (das ist dann die tp.xls) und darauf mit der E8.xlsm die Bezüge gelegt. Da mir manche Felder, die in der benötigten Form nicht vorhanden sind, noch errechnen lassen muss, habe ich das für den elegantesten Weg gehalten, weil ich dann in der E8.xlsm genau die Daten aufbereitet vorfinde, wie ich sie benötige.

    Wie man da etwas summieren oder doppelte Versandkosten ausschließen kann, gehört für mich zur Gattung Böhmische Dörfer.

    Es ist nicht so, dass ich hier im Forum eine einfache, schnelle und günstige Lösung suche, sondern mit fehlt eine die Qualifikation und die Zeit, mich in ein so komplexes Thema einzuarbeiten, zumal, wie man an meinen Beiträgen sieht, so alle 1-2 Jahre mal was suche/brauche, und ich bis dahin wahrscheinlich wieder alles vergessen, hätte, was ich bis dahin gelernt habe. Ähnlich diesem Fall, wo ich die vorhandene Anwendung nicht auf meine Bedrüfnisse umbiegen konnte.
     
  10. Hallo Wolfgang,

    ich kann dir die paar Makro-Zeilen schreiben. Du mußt nur sagen, was es können muß.

    Beschreib doch mal wann und wie Zeile 7 und 10 in tp.xls addiert werden:
    Wann ? Wenn gleiche Werte in Spalte A, J, K ?
    Wie ? A7:G7 bleibt; H7 ?; I7=I7+I10; J7=J7+J10; K7=K7+K10; anschließend Zeile 10 löschen

    Darf TP.xls nach Spalte A, J, K sortiert werden ?

    Gruß Matjes :)
     
Die Seite wird geladen...

Makros und anderes - Excel - Ähnliche Themen

Forum Datum
Makros Outlook 2010 Windows XP Forum 31. Okt. 2012
WORD: Makros deaktiviert?? Microsoft Office Suite 16. Apr. 2009
Visual Basic-Befehle oder Makros aufzeichnen - Befehlsketten automatisieren? Windows XP Forum 5. Apr. 2009
Documents2Go Excel Makros Windows XP Forum 29. Apr. 2008
excel-makros in staroffice und openoffice StarOffice, OpenOffice und LibreOffice 6. März 2008