PDA

Vollständige Version anzeigen : Kriterien aus mehreren Tabellenblätter prüfen


Artistline
03.06.2013, 11:27
Hallo zusammen,

ich habe leider für mein Problem keinen Ansatz bisher.
Ich würde gerne Rechnungen überprüfen. Dies soll mit VBA geschehen da die Rechnungen öfters aus SAP exportiert werden und somit einfacher zu händeln sind.

Anbei habe ich eine Text Datei hochgeladen um besser zu veranschaulichen.

Es gibt folgende Tabellenblätter: Rechnung, Report, Leistungskatalog.

Das Tabellenblatt "Rechnungen" beinhaltet Fallnummern, Namen, Ziffer, Anzahl, Faktor und Grundpreis.
Ausschlaggebend ist der Grundpreis. Dieser muss mit Tabellenblatt "Leistungskatalog" übereinstimmen. Allerdings muss der Grundpreis geteilt durch Faktor und geteilt durch Anzahl gerechnet werden um auf das Ergebnis im Leistungskatalog zu kommen.

Es muss also jede Zeile im Tabellenblatt Rechnungen überprüft werden.

Beispiel: Semmel Knoedel hat die Fallnr 1111223 im Tabellenblatt Rechnungen somit die Ziffer 5370. Die Fallnr 1111223 taucht im Tabellenblatt "Report" gleich 2 mal auf. Für die Leistung ergibt sich : RNSCTCNNHA und RNSCTCNNHC
Jetzt muss im Leistungskatalog in der Spalte Leistung überprüft werden ob es für RNSCTCNNHA oder RNSCTCNNHC eine entsprechende Ziffer mit der nr. 5370 gibt. Falls ja dann wird weiter überprüft ob die Rechnung stimmt. Falls die Ziffer sich in der Spalte D befindet dann gilt der Preis in der Spalte E. Falls der Preis in der Spalte F ist dann gilt der Preis G usw. Falls es keine Ziffernr gibt dür die Leistungen dann soll im Tabellenblatt "Rechnungen" die Zeile rot markiert werden.
In unserem Beispiel haben wir für 5370 einen Preis von 116,57. Jetzt muss noch überprüft werden ob der Preis stimmt, ansonsten wird die Zeile im Tabellenblatt Rechnungen rot markiert. D.h. Grundpreis von Tabelle Rechnungen (11657), wie oben beschrieben, wird durch Faktor geteilt und durch Anzahl geteilt und muss den selben Betrag ergeben wie im Tabellenblatt Leistungskatalog für die gesuchten Spalten (in dem Fall Spalte E). In diesem Fall stimmen beide Bedingungen überein und somit muss im Tabellenblatt Rechnung diese Zeile nicht rot markiert werden. Jetzt kommt die nächste Zeile dran....usw.

Hoffe Ihr habt eine Idee wie man das per VBA lösen kann.

Gruß Artistline

ASE
03.06.2013, 12:20
Hallo Artistline,
dabei kommt die Stelle, wo man genauer hinschauen muss.
Für die Rechnung 1111223 existieren im Report zwei Einträge. Welches "Schweindel" darf es denn sein? Software rechnet zwar schnell aber "hellsehen" kann sie meines Kenntnistandes nicht. Also wie soll es denn gehen?

Hasso
03.06.2013, 13:04
Hallo Artistline,

ich habe mal versucht, das umzusetzen und hoffe, dass das Ergebnis deinen Vorstellungen entspricht. Ich habe im Blatt "Rechnung" noch eine zusätzliche Spalte eingefügt, in die der Preis, der sich aus dem Leistungskatalog ergibt, eingetragen wird. Dann kann man das besser kontrollieren.Option Explicit

Private Sub CommandButton1_Click()
Dim rngZelleRechnung As Range
Dim rngZelleReport As Range
Dim rngZelleLeistung As Range
Dim rngZelle As Range
Dim intZeile As Integer
Dim intLetzteZeileRechnung As Integer
Dim intLetzteZeileReport As Integer
Dim intLetzteZeileLeistung As Integer
Dim intTreffer As Integer
Dim sngPreis As Single

intLetzteZeileRechnung = Worksheets("Rechnung").Cells(Rows.Count, 1).End(xlUp).Row
intLetzteZeileReport = Worksheets("Report").Cells(Rows.Count, 1).End(xlUp).Row
intLetzteZeileLeistung = Worksheets("Leistungskatalog").Cells(Rows.Count, 1).End(xlUp).Row
intTreffer = 0
Worksheets("Rechnung").Range("A2:H" & intLetzteZeileRechnung).Interior.ColorIndex = xlColorIndexNone

For Each rngZelleRechnung In Worksheets("Rechnung").Range("B2:B" & intLetzteZeileRechnung)
sngPreis = rngZelleRechnung.Offset(0, 5) / rngZelleRechnung.Offset(0, 4) / rngZelleRechnung.Offset(0, 3)
For Each rngZelleReport In Worksheets("Report").Range("A2:B" & intLetzteZeileReport)
If rngZelleRechnung = rngZelleReport Then
For Each rngZelleLeistung In Worksheets("Leistungskatalog").Range("B2:B" & intLetzteZeileLeistung)
If rngZelleRechnung.Offset(0, 2) = rngZelleLeistung.Offset(0, 2) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 3)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 2) = rngZelleLeistung.Offset(0, 4) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 5)
intTreffer = 1
Exit For
End If
Next rngZelleLeistung
'Wenn kein Treffer oder Preis abweichend, Zeile rot färben
If intTreffer = 0 Or (sngPreis <> rngZelleLeistung.Offset(0, 3) And sngPreis <> rngZelleLeistung.Offset(0, 5)) Then
Worksheets("Rechnung").Range("A" & rngZelleRechnung.Row & ":H" & rngZelleRechnung.Row).Interior.Color = RGB(255, 0, 0)
Else
Worksheets("Rechnung").Range("A" & rngZelleRechnung.Row & ":H" & rngZelleRechnung.Row).Interior.ColorIndex = xlColorIndexNone
intTreffer = 0
End If
Exit For
End If
Next rngZelleReport
Next rngZelleRechnung

End SubBeispielmappe anbei.

Artistline
03.06.2013, 13:12
Hallo ASE,


Es gibt 2 Reports für diese Fallnummer, da hast du recht. Wir haben ja noch aus dem Tabellenblatt Rechnung die Spalte Ziffer. Die Kombination aus der Leistung und Ziffer, ergibt in der Tabelle den gleichen Wert in diesem Fall. Ich habe nicht alle Daten in der Tabelle eingetippt. Sonst würde in der Tabelle Rechnung noch ein Eintrag für genau diesen Fall stehen. Und hier hätten wir ebenfalls den selben Betrag. Daher stimmt die Berechnung.
Kann man nicht eine Prozedur laufen lassen um mit der Fallnr. xy aus Tabelle Rechnungen alle Leistungen dazu aus Tabelle Report mit der Tabelle Leistungskatalog vergleichen kann? Hoffe ich habe mich verständlich ausgedruckt.....

Edit: @Hasso : Vielen Dank für deine Arbeit. Ich werde es gleich mal testen und Feedback geben. Muss es in die eigentliche Tabelle einsetzen. Da sind viel mehr Spalten, daher anpassen und testen.
Gibt es die Möglichkeit den Code von VBA projekt(Personl.xls) zu starten? Dann muss man nicht den Code jedes Mal kopieren weil aus SAP jeden Monat verschiedene xls Dateien exportiert werden.

Gruß
Artistline

Artistline
04.06.2013, 08:30
Guten Morgen zusammen,

ich habe nochmal den Bereich für den Leistungskatalog erweitert da es die anderen Preise nicht erfasst hatte. Bekomme leider eine Fehlermeldung: Laufzeitfehler 6 Überlauf

Habe auch die volle Tabelle mit allen Spalten hier hochgeladen bzw. entsprechend den Code erweitert.

Vorher wurde in der Spalte H der Preis erzeugt zu den einzelnen Zeilen. Jetzt sind dont andere Daten vorhanden. Wie bekomme ich es hin das bei Tabellenblatt Rechnungen in der Spalte T der Preis ausgegeben wird.

Hierzu nochmal eine Frage wegen Rotfärben der Zeilen:
Müsste es nicht anstatt AND ein Or sein?
So wie ich es verstehe, wird der sngPreis mit den einzelnen Leistungen verglichen bzw. soll ungleich sein. Falls es aber bei Offset(0,3) gleich ist dann soll es nicht rot gefärbt werden. Durch And Befehl werden ja auch die anderen Felder geprüft und da es dann nicht zutrifft, wird alles rot gefärbt oder? Evtl. habe ich auch einen Gedankenfehler...

'Wenn kein Treffer oder Preis abweichend, Zeile rot färben
If intTreffer = 0 Or (sngPreis <> rngZelleLeistung.Offset(0, 3) And sngPreis <> rngZelleLeistung.Offset(0, 5) And _
sngPreis <> rngZelleLeistung.Offset(0, 7) And sngPreis <> rngZelleLeistung.Offset(0, 9) And sngPreis <> rngZelleLeistung.Offset(0, 11) And _
sngPreis <> rngZelleLeistung.Offset(0, 13) And sngPreis <> rngZelleLeistung.Offset(0, 15) And sngPreis <> rngZelleLeistung.Offset(0, 17) And _
sngPreis <> rngZelleLeistung.Offset(0, 19) And sngPreis <> rngZelleLeistung.Offset(0, 21) And sngPreis <> rngZelleLeistung.Offset(0, 23) And _
sngPreis <> rngZelleLeistung.Offset(0, 25)) Then
Worksheets("Rechnung").Range("A" & rngZelleRechnung.Row & ":H" & rngZelleRechnung.Row).Interior.Color = RGB(255, 0, 0)
Else
Worksheets("Rechnung").Range("A" & rngZelleRechnung.Row & ":H" & rngZelleRechnung.Row).Interior.ColorIndex = xlColorIndexNone
intTreffer = 0

Der neue Code und die Exceldatei sind im Anhang. Evtl hat jemand eine Idee wie ich sngPreis verschieben kann und wieso die Fehlermeldung auftaucht bzw. ob der Code oben richtig ist.

Gruß und vielen Dank im Voraus.

Artistline

ASE
04.06.2013, 11:40
Hallo Artistline,
ich habe mir durch Zufall den Lösungsansatz angeschaut. Damit besteht immer noch der Fehler, dass wenn man im Report nachschauen will welche Fallnummer zur Leistung gehört man immer die zufällig 1. findet! Warum steht das Leistungskennzeichen nicht mit in der Rechnung?
Oder Du musst im Report eine Referenz zur Rechnung herstellen. Mit Preis und Datum allein kommst Du in den Wald!

Artistline
04.06.2013, 12:18
Hallo ASE,

ich ziehe die Daten so wie sie sind aus SAP. Daher keine andere Möglichkeit.
Es wird in der Hinsicht keine Eindeutigkeit geben weil es auch Abweichungen geben kann. Es ist mehr eine Suchfunktion. Wenn die eine Bedingung erfüllt ist dann werden weitere gesucht und dann erst wenn nichts gefunden wird, soll die Zeile rot markiert werden. Habe leider keine andere Möglichkeit. Allerdings weiss ich nicht was du mit Datum meinst. Datum habe ich gar nicht in der Suchbedingung eingebaut. Mein problem ist das der Code leider falsche Werte ausgibt. Es sucht in der Tabelle Leistung nicht die richtigen Werte. Das heisst, das was ich manuell Suche, sollte auch der Code können. Leider tut es das nicht und berechnet mir den Preis falsch. Z.b. Bei Hans Muster ist der Preis 17,49, allerdings wird hier der Preis nicht richtig berechnet da es ja mal 2,5 also den Faktor und mal Anzahl genommen werden muss.....Ausserdem bringt mir der Code eine Fehlermeldung (siehe oben.)

Habe die Testdatei nochmal hochgeladen....

Also im ersten Schritt bin ich in der ersten Zeile der Rechnung und nehme die Fallnummer aus dem Report und schaue im Leistungskatalog ob es dazu einen Preis gibt der entsprechend ist. Falls nicht dann schaue ich ob in der 2ten Zeilebei Report für die selbe Fallnummer eine andere Leistung gibt und schaue dort ob es einen passenden Preis (von GOÄ Ziffer 1 bis 12) gibt. Erst dann wenn es keinen passenden Preis gibt, wird die Zeile rot markiert. Falls ja, bleibt es so.
Dann schaue ich mir die 2te Zeile in der Rechnung an...usw. Das sollte doch machbar sein oder? Kann keiner helfen? Es ist ja mehr eine Suchfunktion ob Preise gefunden werden....es gibt keine eindeutigen Datensätze hierzu....

Gruß
Artistline

Hasso
04.06.2013, 13:14
Hallo Artistine,Hierzu nochmal eine Frage wegen Rotfärben der Zeilen:
Müsste es nicht anstatt AND ein Or sein?
So wie ich es verstehe, wird der sngPreis mit den einzelnen Leistungen verglichen bzw. soll ungleich sein. Falls es aber bei Offset(0,3) gleich ist dann soll es nicht rot gefärbt werden. Durch And Befehl werden ja auch die anderen Felder geprüft und da es dann nicht zutrifft, wird alles rot gefärbt oder? Evtl. habe ich auch einen Gedankenfehler...
Falls die Ziffer sich in der Spalte D befindet dann gilt der Preis in der Spalte E. Falls der Preis in der Spalte F ist dann gilt der Preis G usw. Falls es keine Ziffernr gibt dür die Leistungen dann soll im Tabellenblatt "Rechnungen" die Zeile rot markiert werden.
In unserem Beispiel haben wir für 5370 einen Preis von 116,57. Jetzt muss noch überprüft werden ob der Preis stimmt, ansonsten wird die Zeile im Tabellenblatt Rechnungen rot markiert. Nein, wenn man an der Stelle ein OR nimmt, den reicht es, wenn einer der Preise nicht mit dem Preis auf der Rechnung übereinstimmt, das wird aber immer der Fall sein. Ich hatte allerdings in deinem Beitrag das Kürzel usw. überlesen und deshalb prüft mein Code nur die Preise in den Spalten E und G.

Warum prüfst du das Ganze nicht einfach mit fiktiven Werten durch und guckst nach, ob die Rotfärbung nur bei den von dir gewünschten Fällen ausgelöst wird?

Artistline
04.06.2013, 13:28
Hallo Hasso,
danke erst mal für deine Antwort. Ich habe ja den Code soweit ich konnte angepasst. Nur bekomme ich mit den fiktiven Werten auch Fehler. Wie weiter oben beschrieben Z.b. Bei Hans Muster ist der Preis 17,49. 17,49 stimmt und trotzdem wird es mir rot angezeigt. Manuell kann man das nachverfolgen. Ich weiss nur leider nicht wieso. 17,49 ist der berechnete sngPreis nach dem Ausführen des Codes. Wieso wird es mir rot angezeigt obwohl dieser Preis stimmt? Wenn man die Gegenrechnung macht (17,49 *2,5*1) komme ich auf 43,73....

Edit: Habe eben auch einen fiktiven Wert extra hinzugefügt. Jetzt wird mir sogar kein sng Preis ausgerechnet...

Hasso
04.06.2013, 13:45
Hallo Artistline,

die Preise im Leistungskatalog sind gerundet, aber der Preis, den ich damit vergleiche ist nicht gerundet. Ersetze mal die ZeilesngPreis = rngZelleRechnung.Offset(0, 5) / rngZelleRechnung.Offset(0, 4) / rngZelleRechnung.Offset(0, 3)durchsngPreis = Round(rngZelleRechnung.Offset(0, 5) / rngZelleRechnung.Offset(0, 4) / rngZelleRechnung.Offset(0, 3), 2)

Artistline
04.06.2013, 13:59
sind schon paar Fehler weniger angestrichen aber irgendwie wird mir der sng Preis nicht berechnet. In der Eigentlichen Tabelle rechnet es mir den sgnPreis aus. Bekomme aber für "Karl Test" komplett falschen Wert angezeigt. Inder Testversion zeigt es mir nur 3 Werte an (siehe Anlage).
Wäre klasse wenn du evtl nachschauen könntest warum die Werte nicht mehr angezeigt werden und wieso bei Karl Test ein falscher sngPreis berechnet wird...

Hasso
04.06.2013, 14:23
Hallo Artistline,Bekomme aber für "Karl Test" komplett falschen Wert angezeigt.Verstehe ich nicht. Grundpreis ist 1166, geteilt durch 100 und geteilt durch 1 ergibt 11,66 - und genau dieser Preis steht auch im Leistungskatalog bei RNSNAB (Zelle E457). Was stimmt da nicht???

Artistline
04.06.2013, 14:36
Hallo Hasso,

also ich bekomme in der Test Datei kein sngPreis dafür angezeigt.
In der Eigentlichen Tabelle mit richtigen Daten (genau die selben Werte) hat es mir den falschen Wert ausgesucht und rot markiert (sngWert 23,32 anstatt 11,66). Habe die gleichen Werte aber anderen Namen in die Testdatei eingefügt. (siehe Screenshot)
Falls ich es richtig verstanden habe, wird der sngPreis direkt nach dem Ausführen des Codes in die Spalte Preis ausgegeben (Tabellenblatt Rechnung).

Hier mein Code für Test und Ursprungsdatei:

Option Explicit

Private Sub CommandButton1_Click()
Dim rngZelleRechnung As Range
Dim rngZelleReport As Range
Dim rngZelleLeistung As Range
Dim rngZelle As Range
Dim intZeile As Integer
Dim intLetzteZeileRechnung As Integer
Dim intLetzteZeileReport As Integer
Dim intLetzteZeileLeistung As Integer
Dim intTreffer As Integer
Dim sngPreis As Single

intLetzteZeileRechnung = Worksheets("Rechnung").Cells(Rows.Count, 1).End(xlUp).Row
intLetzteZeileReport = Worksheets("Report").Cells(Rows.Count, 1).End(xlUp).Row
intLetzteZeileLeistung = Worksheets("Leistungskatalog").Cells(Rows.Count, 1).End(xlUp).Row
intTreffer = 0
Worksheets("Rechnung").Range("A2:S" & intLetzteZeileRechnung).Interior.ColorIndex = xlColorIndexNone

For Each rngZelleRechnung In Worksheets("Rechnung").Range("B2:B" & intLetzteZeileRechnung)
sngPreis = Round(rngZelleRechnung.Offset(0, 10) / rngZelleRechnung.Offset(0, 9) / rngZelleRechnung.Offset(0, 8), 2)
For Each rngZelleReport In Worksheets("Report").Range("B2:B" & intLetzteZeileReport)
If rngZelleRechnung = rngZelleReport Then
For Each rngZelleLeistung In Worksheets("Leistungskatalog").Range("B2:B" & intLetzteZeileLeistung)
If rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 2) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 3)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 4) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 5)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 6) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 7)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 8) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 9)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 10) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 11)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 12) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 13)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 14) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 15)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 16) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 17)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 18) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 19)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 20) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 21)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 22) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 23)
intTreffer = 1
Exit For
ElseIf rngZelleRechnung.Offset(0, 7) = rngZelleLeistung.Offset(0, 24) Then
rngZelleRechnung.Offset(0, 6) = rngZelleLeistung.Offset(0, 25)
intTreffer = 1
Exit For
End If
Next rngZelleLeistung
'Wenn kein Treffer oder Preis abweichend, Zeile rot färben
If intTreffer = 0 Or (sngPreis <> rngZelleLeistung.Offset(0, 3) And sngPreis <> rngZelleLeistung.Offset(0, 5) And _
sngPreis <> rngZelleLeistung.Offset(0, 7) And sngPreis <> rngZelleLeistung.Offset(0, 9) And sngPreis <> rngZelleLeistung.Offset(0, 11) And _
sngPreis <> rngZelleLeistung.Offset(0, 13) And sngPreis <> rngZelleLeistung.Offset(0, 15) And sngPreis <> rngZelleLeistung.Offset(0, 17) And _
sngPreis <> rngZelleLeistung.Offset(0, 19) And sngPreis <> rngZelleLeistung.Offset(0, 21) And sngPreis <> rngZelleLeistung.Offset(0, 23) And _
sngPreis <> rngZelleLeistung.Offset(0, 25)) Then
Worksheets("Rechnung").Range("A" & rngZelleRechnung.Row & ":H" & rngZelleRechnung.Row).Interior.Color = RGB(255, 0, 0)
Else
Worksheets("Rechnung").Range("A" & rngZelleRechnung.Row & ":H" & rngZelleRechnung.Row).Interior.ColorIndex = xlColorIndexNone
intTreffer = 0
End If
Exit For
End If
Next rngZelleReport
Next rngZelleRechnung

End Sub



Hast du mal die Testdatei aufgemacht? Bzw. führt es den Code richtig aus bei dir?

Hasso
04.06.2013, 15:04
Hallo Artistline,

in der von dir geschickten Testdatei wurde gar kein Preis mehr eingetragen. Ich hab das jetzt mal prvisorisch unter "Ziffernart" gemacht. Außerdem sind bei den neuen Daten im Blatt Rechnung jetzt in Spalte A nicht mehr überall Ziffern. Ich hatte aber aus Spalte A die letzte Zeile ermittelt, bis zu der die Rechnungsdaten durchlaufen werden sollen. Ich habe jetzt dafür Spalte B verwendet. Ich hoffe, da steht immer was drin. Dazu mussintLetzteZeileRechnung = Worksheets("Rechnung").Cells(Rows.Count, 1).End(xlUp).Rowgeändert werden inintLetzteZeileRechnung = Worksheets("Rechnung").Cells(Rows.Count, 2).End(xlUp).Row

Artistline
05.06.2013, 08:08
Guten Morgen Hasso,

vielen herzlichen Dank erst mal für deine Hilfe.
Macht auch Sinn jetzt. Habe allerdings bei Report und Leistungskatalog ebenfalls intLetzteZeileRechnung auf 2 gesetzt da dort immer ein Wert steht.

Allerdings bekomme ich immer noch falsche Werte (siehe Anhang). Habe hier jetzt viele Daten eingetragen(fast wie original Datei). Namen nicht angegeben da diese eh für die Berechnung nicht relevant sind. Bekomme fast immer den Wert 4,37 bzw auch der Wert 23,32 ist falsch (siehe oben anstatt 11,66). Sehr viele Zeilen werden falsch berechnet und rot markiert. Weiss leider nicht woran das liegt....wäre dir sehr verbunden wenn du rausfinden könntest woran das liegt....

2te Frage: Wo genau im Code gibst du unter Ziffernart den Preis wieder bzw. wie kann ich den Wert in eine andere Spalte eintragen? Sehe es leider nicht...

Danke und Gruß
Artistline

Hasso
05.06.2013, 08:54
Hallo Artistline,

die Fallnummern gibt es mehrfach - das war war mir in den bisherigen Beispieldateien nicht aufgefallen. Das verkompliziert die Sache. Dann wird beim derzeitigen Code in der Tabelle "Report" immer der Leistungscode für das erste Auftreten der Fallnummer ausgewählt. Da muss ich erst mal einmal überlegen, wie man das korrigieren kann. Möglich ist das sicher.

Ich melde mich, wenn ich einen Lösungsansatz gefunden habe.

Artistline
05.06.2013, 09:00
Hallo Hasso,

danke dir herzlich...war am verzweifeln warum das nicht funktioniert...
Würde auch gerne wissen, wo genau im Code gibst du unter Ziffernart den Preis wieder bzw. wie kann ich den Wert in eine andere Spalte eintragen lassen? Sehe es leider nicht...

Gruß
Artistline

Hasso
05.06.2013, 09:02
Hallo Artistline,Würde auch gerne wissen, wo genau im Code gibst du unter Ziffernart den Preis wieder bzw. wie kann ich den Wert in eine andere Spalte eintragen lassen?Hier: sngPreis = Round(rngZelleRechnung.Offset(0, 10) / rngZelleRechnung.Offset(0, 9) / rngZelleRechnung.Offset(0, 8), 2)
rngZelleRechnung.Offset(0, 6) = sngPreis


Noch eine Nachfrage:

Stehen in der Tabelle Report gleiche Fallnummern immer direkt untereinander? Das würde die Sache erheblich erleichtern!

Für diesen Fall habe ich folgende Lösung. Ersetze überall die Zeile intTreffer = 1
Exit For
durch intTreffer = 1
'Prüfen, ob in der nächsten Zeile der gleiche Leistungscode steht
If rngZelleReport <> rngZelleReport.Offset(1, 0) Then Exit For

Beispielmappe als Anlage.

Artistline
05.06.2013, 09:32
Hallo Hasso, wir kommen schon der Sache näher.

Ich bekomme allerdings einen Laufzeitfehler 91 "Objektvariable oder with-blockvariable nicht festgelegt:
Habe den Code in die Excel Datei mit real Daten eingefügt...

es springt mir beim debuggen hierher : bekomme an dieser Stelle den sngPreis 116,57 angezeigt

If intTreffer = 0 Or (sngPreis <> rngZelleLeistung.Offset(0, 3) And sngPreis <> rngZelleLeistung.Offset(0, 5) And _
sngPreis <> rngZelleLeistung.Offset(0, 7) And sngPreis <> rngZelleLeistung.Offset(0, 9) And sngPreis <> rngZelleLeistung.Offset(0, 11) And _
sngPreis <> rngZelleLeistung.Offset(0, 13) And sngPreis <> rngZelleLeistung.Offset(0, 15) And sngPreis <> rngZelleLeistung.Offset(0, 17) And _
sngPreis <> rngZelleLeistung.Offset(0, 19) And sngPreis <> rngZelleLeistung.Offset(0, 21) And sngPreis <> rngZelleLeistung.Offset(0, 23) And _
sngPreis <> rngZelleLeistung.Offset(0, 25)) Then

Woran könnte es liegen? habe eigentlich die selben Werte benützt...nur die weiteren Datenfelder sind in der Testdatei gelöscht....

Das mit der Fallnummer ist ja kein Problem das die untereinander stehen...brauche ja nur aufsteigend zu sortieren...das geht....nur die Fehlermeldung verstehe ich nicht.
Und ich bekomme nur für den Fall 2201085 den falschen Wert angezeigt...es wird dann nicht weiter gerechnet...bzw. die anderen Werte werden nicht mehr überprüft...

Habe die Quelldatei im Anhang eingefügt....wieso zum Teufel bringt er mir einen Fehler? Sind doch die selben Felder....

Edit: Habe gemerkt das wenn ich die Fallnr bei Rechnung bzw. Leistung aufsteigend sortiere damit die Fallnr direkt unter einander stehen, ich somit diese Fehlermeldung bekomme...dachte die Fallnr. bei Report sollen direkt untereinander stehen?....Gibt es doch evtl eine andere Lösung dafür? Wenn ich das nächste mal aus SAP Daten ziehe, werde ich dieses Problem wahrscheinlich wieder haben...z.B. beim Report ist bei deiner Tabelle die Fallnr 2203304 mehrfach vorhanden und steht nicht immer direkt untereinander...trotzdem funktioniert der Code bei deiner hochgeladenen Datei...irgendwie kapier ich es nicht :(

Hasso
05.06.2013, 11:10
Hallo Artistline,

es ist leider nicht so ganz einfach, den Prüfvorgang Schritt für Schritt nachzuvollziehen und dann in entsprechenden Code umzusetzen. Daher hab bitte noch etwas Geduld. Ich melde mich auf jeden Fall.

Artistline
05.06.2013, 11:28
Hallo Hasso,

vielen lieben Dank für deine Mühen...klar habe ich geduld ;)
Ich versuche ja nebenher auch die Schritte nachzuvollziehen...bin leider nicht so geübt in VBA...

Gruß und nochmals Danke
Artistline

Hasso
05.06.2013, 17:27
Hallo Artistline,

ich habe den Code jetzt mal neu geschrieben und ausführlich kommentiert. Allerdings habe ich dass Gefühl, dass meine Gehirnwindungen langsam heißlaufen!

Teste das bitte mal.

Artistline
06.06.2013, 08:56
Guten Morgen Hasso,

sorry das du soviel Mühen hast...danke dir herzlich.
Stimmt leider immer noch nicht ganz: Z.b. bei Fallnr 2202324 für Preis 17,49 stimmt der Grundpreis. Weiss jetzt leider nicht warum es trotzdem rot markiert wurde. Auch bei Fallnr 2205187 für Preis 16,32 ist der Grundpreis richtig....so zieht es sich durch alle rote Markierungen leider....

1te Frage: Ich habe den Code ausgeführt. Die Zeilen wurden rot markiert. Habe dann, um das ganze nochmal zu testen, die Ziffer (Spalte I) auf den Wert "1" bzw. "5000" gesetzt. Habe den Code nochmal ausgeführt und diese Zeile blieb weiss bzw. wurde nicht markiert obwohl die Ziffer 5000 gar nicht existiert bzw. die Zahl 1 einen anderen Wert ergibt. Wenn ich den Code 2 mal ausführe, müsste der Code doch die Zeilen alle nochmal überprüfen und mir die Werte erneut markieren oder?
Habe auch den Grundpreis in Spalte "L" verändert und den Code nochmal ausgeführt, auch hier wurde die Zeile nicht rot markiert.
Es sind ja 3 Kriterien, die auf Richtigkeit überprüft werden müssen. Einmal der Preis und dann nochmal die Ziffer im Rechnungsblatt und ob die Fallnummer im Rechnungsblatt auch im Report existiert. Falls eine von denen nicht stimmt, muss sogar die Zeile rot gefärbt werden da die Angaben dann sicherlich nicht stimmen können.

2te Frage als ich den Code mir angeschaut habe:

If intZiffer = rngZelleLeistung.Offset(0, intPreisZaehler) Then 'intZiffer = rngZelleLeistung.Offset(0, 2)
intTreffer = 1
Exit For
Wo genau ist der intPreisZaehler definiert?

Gruß und lieben Dank
Artistline

Artistline
11.06.2013, 08:22
Hallo Hasso, wahrscheinlich hast du schon genug von mir aber habe hier nochmal eine andere Lösung....leider wird hier aber nicht weiter gesucht. Es sind viele rote Zeilen markiert.
Evtl. hast eine Idee wie man den Code verbessern kann oder sogar eine ganz andere Lösung? Es ist wirklich zum verzweifeln langsam...

Gruß
Artistline

Hasso
11.06.2013, 08:29
Hallo Artistline,Es sind viele rote Zeilen markiert.Hast du denn geprüft, ob die zu Recht rot sind (weil es keinen Treffer gibt oder der Preis falsch ist)? Dazu habe ich keine Lust.

Artistline
11.06.2013, 10:21
Hallo Hasso,

sicherlich habe ich überprüft ob die Zeilen richtig sind, sonst würde ich es nicht schreiben...will dich ja nicht ärgern...siehe weiter oben...


Stimmt leider immer noch nicht ganz: Z.b. bei Fallnr 2202324 für Preis 17,49 stimmt der Grundpreis. Weiss jetzt leider nicht warum es trotzdem rot markiert wurde. Auch bei Fallnr 2205187 für Preis 16,32 ist der Grundpreis richtig....so zieht es sich durch alle rote Markierungen leider..

Wenn ich im nachhinein auch die Ziffer oder den Grundpreis mit falschen Angaben zu Testzwecke ändere und dann den Code ausführe, werden diese Zeilen mir auch nicht rot markiert...

Gruß
Artistline

Hasso
11.06.2013, 11:28
Hallo Artistline,Stimmt leider immer noch nicht ganz: Z.b. bei Fallnr 2202324 für Preis 17,49 stimmt der Grundpreis.Ändere mal diesen Teil im Code:If rng <> WorksheetFunction.Round(rng.Offset(, 4) / (rng.Offset(, 3) * rng.Offset(, 2)), 2) Then Worksheets("Rechnung").Rows(rng.Row).Interior.ColorIndex = 3in If Not rng = WorksheetFunction.Round(rng.Offset(, 4) / (rng.Offset(, 3) * rng.Offset(, 2)), 2) Then
Worksheets("Rechnung").Rows(rng.Row).Interior.ColorIndex = 3
End IfDann sind zumindest die von dir angeführten Beispiele nicht mehr rot markiert.

Artistline
11.06.2013, 12:44
Hallo Hasso,

danke nochmal für deine Geduld...hat leider nicht geklappt...sehr viele Zeilen mit Fehler markiert die keine sind. Anscheinend gibt es dafür keine Lösung oder wir kommen nicht einfach drauf...schade...dachte das man es mit vba hinbekommen könnte....die Werte werden leider nicht richtig berechnet und auch falsch rot markiert...z.b. für Fallnr 2202324 wird ein Preis von 4,08 berechnet. Müsste aber 4,37 heissen(Zeile 10, Tabelle Rechnung).
Weiteres Beispiel: Fallnr 2203304 (Zeile 25, Tabelle Rechnung) wird wieder ein Wert 4,08 berechnet. Weiss nicht woher dieser Wert überhaupt herkommt. Wenn man sich den Report zu diesem Fall anschaut sind sehr viele Leistungen angegeben. Der Code rechnet aber anscheinend nur bis zu der ersten Leistung und gibt dann den Wert als falsch aus. Wenn jetzt weiter nachgeschaut werden würde für die anderen Leistungen, dann würde man auch den Wert 6,54 finden für die Leistung RNSTH (F537; Tabelle Leistungskatalog). Somit wäre es kein Fehler. Wie schon geschrieben, ist es eine Suchfunktion wo Werte mit einander verglichen werden und wenn kein Wert gefunden wird, erst dann soll die Zeile rot markiert werden. Es wird aber immer nur bis zu der ersten Leistung gerechnet die gefunden wird und falls da der Wert nicht stimmt, wird nicht weiter berechnet. Genau das sollte aber passieren da ja im Leistungskalatog viele andere Werte vorhanden sind.

Sorry wegen den Umständen und dieser Aufgabe...

Gruß
Artistline

Hasso
11.06.2013, 12:56
Hallo Artistline,

es ist für mich sehr schwer, den Durchlauf des Codes zu verfolgen. Am Besten wäre es, die Rotfärbung nicht erst zum Schluss auszuführen, sondern immer dann, wenn die entsprechende Fallnummer abgearbeitet wurde. Ich werde das noch mal versuchen, weiß aber nicht, ob es erfolgreich sein wird.

Du hast sicher Recht, dass das mit VBA gehen muss - nur wie genau, ist das Problem. Zumal die Tabellen ja nicht so klar aufgebaut sind. Eine Fallnummer kommt auf dem Blatt "Rechnung" eventuell mehrfach vor, ebenso auf dem Blatt "Report". Das erschwert das Erstellen des Codes zusätzlich.

Hilfreich ist es auch, m Anfang die Rotfärbungen erst mal zurückzusetzen. Das hatte ich schon mehrfach eingebaut, aber bei jeder neuen Version von dir ist das erst mal wieder raus. Gleiches gilt für die Spalte H in der Rechnung, wo ich auch erst mal alle Werte löschen würde.

NEU:

Ich muss leider noch einmal nachfragen.

In den Zeilen 8 und 10 gibt es identische Fallnummern (2202324) und Ziffern (5298). Der Grundpreis ist aber unterschiedlich (656 bzw. 473). Beim Durchlaufen des Codes würde doch hier im Leistungskatalog der gleiche Preis gefunden. Also muss einer in der Rechnung auf jeden Fall falsch sein - oder kapiere ich das nicht richtig?

Artistline
11.06.2013, 13:23
Hallo Hasso,

sorry wegen den Umständen....dachte so siehst Du besser die Fehler...werde es natürlich in Zukunft nicht mehr rotfärben bzw. die Werte löschen....

Da hast du recht....es gibt keine eindeutigen Datensätze...eher muss der Code wie eine Suchfunktion reagieren und mit verschiedenen Bedingungen nach den Werten schauen, falls alle Werte gesucht und nicht gefunden werden, soll erst dann die Zeile rot markiert werden.....Das ist sicherlich nicht einfach und mit meinen bedüftigen VBA Kentnisse sehe ich wenig Chancen auf Erfolg....

Daher herzlichen Dank nochmals...

Gruß
Artistline

Hasso
11.06.2013, 13:30
Hallo Artistline,

hast du meine Ergänzung zu meinem letzten Beitrag (#29 (http://www.ms-office-forum.net/forum/showpost.php?p=1517444&postcount=29)) gelesen?

Artistline
11.06.2013, 14:04
Hallo Hasso,


In den Zeilen 8 und 10 gibt es identische Fallnummern (2202324) und Ziffern (5298). Der Grundpreis ist aber unterschiedlich (656 bzw. 473). Beim Durchlaufen des Codes würde doch hier im Leistungskatalog der gleiche Preis gefunden. Also muss einer in der Rechnung auf jeden Fall falsch sein - oder kapiere ich das nicht richtig?

Bei der Tabelle Report gibt es zu der Fallnr 2202324 die Leistung RNSTHST und RNSABD. Ziffer 5298 kommt als 2te Variable dazu. Jetzt muss ich im Leistungskatalog zu den einzelnen Leistungen (RNSTHST und RNSABD) solange schauen bis ich für Zeile 8 im Blatt Rechnung den Wert 656 gefunden habe. Für Zeile 7 und 8 gibt es keine Lösung, also rot markiert. Für Zeile 10 gibt es die Lösung 437, also kein Fehler.

Um es besser zu verstehen, solltest du aber dir den Fall 2203304 (Zeile 25) anschauen. Denn da wirst du sehen das es sogar zu den einzelnen Leistungen und Ziffern auch mehrere Lösungen also Preise gibt. Jetzt muss der Code nach allen möglichen Lösungen suchen und wenn da der Grundpreis (von Tabelle Rechnung) nicht dabei ist, sollte die Zeile rot markiert werden. Genau das ist entscheidend für den Code und die Richtigkeit der Funktion.

Gruß
Artistline

Hasso
11.06.2013, 15:24
Hallo Artistline,Weiteres Beispiel: Fallnr 2203304 (Zeile 25, Tabelle Rechnung) wird wieder ein Wert 4,08 berechnet. Weiss nicht woher dieser Wert überhaupt herkommt.Aus G543 im Leistungskatalog - dort findet man nämlich die Leistung RNSTHST (die in "Report" I11 zur Fallnr 2203304 gefunden wurde) und die Ziffer 5298.

Artistline
12.06.2013, 07:26
Hallo Hasso,

mittlerweile hatte ich es auch gefunden, genau dieser fall ist ein gutes Beispiel dafür wie der Code funktionieren sollte da mehrere Lösungen möglich sind (wie weiter oben beschrieben). Hier muss halt noch der Grundpreis mit dem gefunden Preis aus Leistungskatalog verglichen werden, wenn der Wert nicht gleich ist dann weitersuchen bis der Wert gefunden wird. Sonsten Zeile rot markieren....

Weiss nur nicht wie man das im Code ändern kann...

Gruß
Artistline

Hasso
13.06.2013, 07:22
Hallo Artistline,

ich habe immer noch große Verständnisprobleme, was den Aufbau der Tabellen und den Ablauf angeht.

Hierzu ein Beispiel:

Im Blatt "Rechnung" haben wir in Zeile 22 die Fallnummer 2203304. Diese wird jetzt im Blatt "Report" gesucht. For Each rng In Worksheets("Rechnung").Range("B2:B" & Worksheets("Rechnung").Cells(Rows.Count, 2).End(xlUp).Row)
With Worksheets("Report")
Set c = .Columns(2).Find(what:=rng, LookIn:=xlValues, lookat:=xlWhole)

Wird sie gefunden, findet man in Spalte I die Leistung. Dann wird im Leistungskatalog gesucht, ob zu dieser Leistung die Ziffer aus "Rechnung" Zeile 22 existiert und der zugehörige Preis ermittelt (so vorhanden). Wird zur ersten Leistung und der Ziffer kein Preis gefunden, wird es in "Report" mit der nächsten Leistung versucht usw.
Nun finden wir aber in "Report" zur Ziffer 2203304 die Leistung RNSTH 5 mal! Was ergibt das für einen Sinn? Es wird jetzt 5 mal der gleiche Suchvorgang im Leistungskatalog gestartet:

<b>Report</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:48px;" /><col style="width:56px;" /><col style="width:69px;" /><col style="width:59px;" /><col style="width:59px;" /><col style="width:63px;" /><col style="width:69px;" /><col style="width:23px;" /><col style="width:95px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Patient</td><td >Fall</td><td >Nachname</td><td >Vorname</td><td >Adressat</td><td >VeranlOE</td><td >Veranl. FA</td><td >LK</td><td >Leistung</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >&nbsp;</td><td style="text-align:right; ">2201085</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSTHPA</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >&nbsp;</td><td style="text-align:right; ">2201154</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSTHL</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >&nbsp;</td><td style="text-align:right; ">2201873</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSCTCNNHA</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >&nbsp;</td><td style="text-align:right; ">2201873</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSCTCNNHC</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >&nbsp;</td><td style="text-align:right; ">2202324</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSTHST</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >&nbsp;</td><td style="text-align:right; ">2202324</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSABD</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >&nbsp;</td><td style="text-align:right; ">2202396</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSNAB</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >&nbsp;</td><td style="text-align:right; ">2202838</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSCTCN</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >&nbsp;</td><td style="text-align:right; ">2202871</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSMRCN</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >&nbsp;</td><td style="text-align:right; ">2203072</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSTH</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >&nbsp;</td><td style="text-align:right; ">2203072</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSTH</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >&nbsp;</td><td style="background-color:#ffffcc; text-align:right; ">2203304</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">RNSTHST</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >&nbsp;</td><td style="background-color:#ffffcc; text-align:right; ">2203304</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">RNSTHL</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >&nbsp;</td><td style="background-color:#ffffcc; text-align:right; ">2203304</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">RNSTH</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >&nbsp;</td><td style="background-color:#ffffcc; text-align:right; ">2203304</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">RNSTH</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >&nbsp;</td><td style="background-color:#ffffcc; text-align:right; ">2203304</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">RNSTH</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >&nbsp;</td><td style="background-color:#ffffcc; text-align:right; ">2203304</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">RNSTH</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >&nbsp;</td><td style="background-color:#ffffcc; text-align:right; ">2203304</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">RNSTH</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >&nbsp;</td><td style="text-align:right; ">2203340</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >RNSCTWH</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

Artistline
13.06.2013, 08:19
Guten Morgen Hasso,

das ist richtig. Der Sinn liegt einfach darin, daß bei Tabelle Report 5 mal diese Leistung für die selbe Fallnr zu verschiedenen Zeiten erbracht wurde.
Was in Rechnung gestellt wird, ist wieder ein anderes Schuh...
Genau das gilt es zu überprüfen da leider Tabelle Rechnung und Report aus verschiedenen Firmen kommen, die in der Hinsicht nicht im SAP miteinander Vernetzt sind...es ist eher Politik da man sich nicht einig wird...Daher brauche ich auch diese Zuweisung was wir hier versuchen zusammen zustellen....

Hoffe ich konnte es dir einigermaßen erklären. Falls du weitere Fragen hast, stell Sie einfach...

Gruß
Artistline

Hasso
14.06.2013, 06:56
Hallo Artistline,

nächste Frage:

Mir ist aufgefallen, dass in der Rechnung zu vielen Fallnummern gar keine Ziffer eingetragen ist (z.B. Zeilen 39-41, 53-55 und viele andere). Ist das OK? Dort kann dann natürlich auch kein passender Wert im Leistungskatalog gefunden werden. Also könnte man sie gleich rot Färben, ohne die ganze Suchaktionen durchzuführen.

Zur Optimierung des Codes würde ich die Zeile lrow = Worksheets("Leistungskatalog").Cells(Rows.Count, 1).End(xlUp).Row
übrigens vor die For Each Schleife setzen, da die letzte Zeile im Leistungskatalog sich ja während der Suche nicht ändert und nicht bei jedem Schleifendurchgang neu berechnet werden muss.

Artistline
14.06.2013, 11:49
Hallo Hasso,

Das ist absolut ok. Wenn keine Ziffer angegeben ist, kann auch nix berechnet werden, daher die Zeile rot markieren.

Habe den Code jetzt umgeändert soweit wie ich es verstanden habe...bekomme aber eine Fehlermeldung:

Sub knödel1()
Dim rng As Range, c As Range, d As Range
Dim spalte As Integer, zeile As Long, lrow As Long
Dim firstaddress As String

lrow = Worksheets("Leistungskatalog").Cells(Rows.Count, 1).End(xlUp).Row

For Each rng In Worksheets("Rechnung").Range("B2:B" & Worksheets("Rechnung").Cells(Rows.Count, 2).End(xlUp).Row)
With Worksheets("Report")
Set c = .Columns(2).Find(what:=rng.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do

zeile = WorksheetFunction.Match(c.Offset(, 7), .Columns(2), 0)
spalte = WorksheetFunction.Match(rng.Offset(, 7), .Rows(zeile), 0) + 1
rng.Offset(, 6).FormulaLocal = "=Index(Leistungskatalog!A1:AA" & lrow & ";" & zeile & ";" & spalte & ")"
If zeile > 0 And spalte > 0 Then Exit Do

End With
Set c = .Columns(2).FindNext(c)
Loop While c.Address <> firstaddress
End If
End With
Next

For Each rng In Worksheets("Rechnung").Range("H2:H" & Worksheets("Rechnung").Cells(Rows.Count, 8).End(xlUp).Row)

If Not rng = WorksheetFunction.Round(rng.Offset(, 4) / (rng.Offset(, 3) * rng.Offset(, 2)), 2) Then
Worksheets("Rechnung").Rows(rng.Row).Interior.ColorIndex = 3
End If
'rng.ClearContents
Next

Exit Sub

fehler:
spalte = 0
zeile = 0
On Error GoTo 0
Resume Next

End Sub

Wahrscheinlich hast du es anders gemeint oder?

Gruß Artistline

Hasso
14.06.2013, 15:20
Hallo Artistline,

mein Code sieht so aus und läuft fehlerfrei:Sub knödel1()
Dim rng As Range, c As Range, d As Range
Dim spalte As Integer, zeile As Long, lrow As Long
Dim firstaddress As String

'Alle Färbungen zurücksetzen
Worksheets("Rechnung").UsedRange.Interior.ColorIndex = xlNone
Worksheets("Rechnung").Range("H2:H" & Worksheets("Rechnung").Cells(Rows.Count, 2).End(xlUp).Row).Clear
lrow = Worksheets("Leistungskatalog").Cells(Rows.Count, 1).End(xlUp).Row

For Each rng In Worksheets("Rechnung").Range("B2:B" & Worksheets("Rechnung").Cells(Rows.Count, 2).End(xlUp).Row)
'Wenn eine Ziffer vorhanden ist:
If rng.Offset(, 7) > 0 Then
With Worksheets("Report")
Set c = .Columns(2).Find(what:=rng, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
With Worksheets("Leistungskatalog")
On Error GoTo fehler
'Zeile, in der die Leistung gefunden wurde:
zeile = WorksheetFunction.Match(c.Offset(, 7), .Columns(2), 0)
'Spalte, in der die Ziffer gefunden wurde:
spalte = WorksheetFunction.Match(rng.Offset(, 7), .Rows(zeile), 0) + 1
'Der Wert wurde gefunden
If zeile > 0 And spalte > 0 Then
rng.Offset(, 6).FormulaLocal = "=Index(Leistungskatalog!$A$1:$AA$" & lrow & ";" & zeile & ";" & spalte & ")"
Exit Do
End If
End With
'Der Wert wurde nicht gefunden - weiter suchen!
Set c = .Columns(2).FindNext(c)
Loop While c.Address <> firstaddress
End If
End With
End If
If Not Round(rng.Offset(0, 6), 2) = Round(rng.Offset(, 10) / (rng.Offset(, 9) * rng.Offset(, 8)), 2) Then
'wenn Preis falsch, rot färben
Worksheets("Rechnung").Rows(rng.Row).Interior.ColorIndex = 3
'wenn keine Ziffer vorhanden, blau färben
If Not rng.Offset(, 7) > 0 Then Worksheets("Rechnung").Rows(rng.Row).Interior.ColorIndex = 17
End If
Next rng
Exit Sub

fehler:
spalte = 0
zeile = 0
On Error GoTo 0
Resume Next

End Sub

Ob er allerdings alle gewünschten Zeilen - und nur diese - rot färbt kann ich nicht garantiern. Dann müsste ich jede Fehlsuche einzeln verfolge und dazu konnte ich mich noch nicht aufraffen.

Artistline
18.06.2013, 10:03
Guten Morgen Hasso,

danke dir herzlich für deine Mühen. Das mit den blaut gefärbten Zeilen finde ich klasse. Wenn ich dein Code ausführe, werden bei Spalte H im Tbl. Rechnung bei manchen Zeilen keine Werte eingetragen. Ich meine nicht die Zeile wo keine Ziffer eingetragen ist sondern dort wo was eingetragen ist, steht trotzdem kein Wert in der Spalte H.Woher kommt das?

Ich habe mich jetzt auf die Fehlersuche gemacht nachdem ich deinen Code ausgeführt habe und es sind noch folgende Fehler enthalten:

Tbl. Rechnung Zeile 10 : Der Preis hier stimmt. Müsste 437 sein bzw. dürfte nicht rot markiert werden.

Tbl. Rechnung Zeile 43: Auch hier stimmt der Preis. Zu finden bei Leistungskatalog G343

Tbl. Rechnung Zeile 47: Preis stimmt. Zu finden bei LK G30

Tbl. Rechnung Zeile 57: Preis stimmt. Zu finden bei LK G537

(siehe Anhang, musst den Code nochmal ausführen da ich die roten Zeilen entfernt habe)

Habe bis hierher getestet. Warum jetzt bei manchen ein Wert eingetragen wird und bei manchen nicht, weiss ich leider nicht.
Das Problem mit den rot markierten Zeilen scheint die selbe zu sein wie vorher. Ich bin ebenfalls chronologisch von oben nach unten durchgegangen. Es scheint so das der Code bei manche Preise nicht weiter sucht falls sie nicht gefunden werden. Sorry echt blöd das es nicht so funktioniert wie gedacht...bist bestimmt schon genervt vom Thema...
Hoffe wir bekommen es bald hin, dann bist erlöst von mir ;-)

Gruß und Danke
Artistline

Hasso
18.06.2013, 14:08
Hallo Artistline,Tbl. Rechnung Zeile 10 : Der Preis hier stimmt. Müsste 437 sein bzw. dürfte nicht rot markiert werdenZeile 10: Fallnummer 2202324, Ziffer 5298
Im Blatt Report taucht die Fallnummer 2202324 zweimal auf, einmal mit der Leistung RNSTHST, einmal mit RNSABD.
Jetzt wird im Leistungskatalog nach RNSTHST und Ziffer 5298 gesucht.
Ergebnis: In G543 steht der Preis 4,08
Würde nach RNSABD und Ziffer 5298 gesucht, würde in Zelle G2 der Preis 4,37 gefunden.
Nach dem ersten Treffer wird aber die Suche abgebrochen, da ja ein Preis gefunden wurde und die Suche nach RNSABD und Ziffer 5298 wird nie durchgeführt. Dann wäre also entscheidend, welche Leistung im Report zuerst kommt. Das ist sicher unbefriedigend.

Das heißt, wir dürfen, wenn ein Preis zu Ziffer und Leistung gefunden wurde, die Suche nicht abbrechen, sondern müssen, wenn der Preis dem errechneten in der Rechnung nicht entspricht, weitersuchen.
Wir müssen also den Code folgendermaßen ändern (meine Änderungen in rot): With Worksheets("Leistungskatalog")
On Error GoTo fehler
'Zeile, in der die Leistung gefunden wurde:
zeile = WorksheetFunction.Match(c.Offset(, 7), .Columns(2), 0)
'Spalte, in der die Ziffer gefunden wurde:
spalte = WorksheetFunction.Match(rng.Offset(, 7), .Rows(zeile), 0) + 1
'Der Wert wurde gefunden:
If zeile > 0 And spalte > 0 Then
'Wenn der Preis dem errechneten in der Rechnung entspricht, dann Suche beenden und Preis eintragen
'Sonst weitersuchen
If Round(rng.Offset(, 10) / (rng.Offset(, 9) * rng.Offset(, 8)), 2) = Round(.Cells(zeile, spalte), 2) Then
rng.Offset(, 6).FormulaLocal = "=Index(Leistungskatalog!$A$1:$AA$" & lrow & ";" & zeile & ";" & spalte & ")"
Exit Do
End If
End If
End With

Artistline
19.06.2013, 13:49
Hallo Hasso,

hab endlich Zeit gefunden um alle Fehler zu durchsuchen....Ergebnis: Einwandfrei :-)... hoffe es bleibt so.
Funtioniert perfekt....vielen herzlichen Dank. Bist wirklich eine Koryphäe :-)

Gruß
Artistline