Hallo, ich bin Thomas und habe mich auf diesem Forum angemeldet durch Empfehlung eines Arbeitskollegen. Wir haben eine kleine Firma und aktuell leider keine Mitarbeiter, welche uns bei dem Problem helfen können. Ich möchte daher selbst ran und das Problem lösen.
Sachverhalt: Wir versenden Listen mit Artikeln (Oft mehrere hundert Zeilen) an unsere Kunden, die Listen haben 11 Spalten.
Problem: Die Listen enthalten für jeden Artikel einen Mehrwertsteuersatz. Entweder 16 % oder 19 % oder eben 0 % oder aber auch 5 % beziehungsweise 7 %. Es gibt noch weitere 3-4 Steuersätze. Allerdings: Es stehen interne Bezeichnungen dort, also anstatt 19 % steht dort die Zahl 56 und anstatt 16 % steht die Zahl 67.
Unsere Kunden können mit diesen Zahlen nichts anfangen und brauchen konkrete Steuersätze wie 19 % usw.
Anbei sende ich euch die 2 Listen, 1 Listen mit den Steuerkennzeichen und die andere Liste aus unserem System.
Allerdings wie erwähnt: Bei jeder Liste steht das Steuerkennzeichen nicht in Spalte K, es kann auch in der Spalte B stehen oder C.
Wie kann ich das am besten lösen, dass unsere Mitarbeiter schnell eine Liste versenden, wo die Werte wie 56 usw. mit Formel in Prozentsätze umgewandelt werden?
Ich kann eventuell Makro aufzeichnen, Formeln eingeben und dann eben Makro abspielen mit Tastenkombination?
Ansonsten habe ich keine weiteren Erfahrungen, außer normale Formeln erstellen.
Kann jemand helfen und das erläutern?
Vielen Dank. :18:
Ich habe mich intensiv heute beschäftigt und einen Makro aufgezeichnet, ich habe die Werte gesucht und ersetzt und dann die Spalte als Prozentsatz formatiert und anscheinend geht es. Kann man es so machen? - Man muss viel manuell bei suchen und ersetzen eingeben, aber das klappt mit makro wunderbar.
Hallo Thomas,
erstmal ein ganz großes "Daumenhoch"!!! Problemstellung sauber beschrieben, ordentliche Beispielmappen UND auch noch selbst an einer Lösung gearbeitet bzw. diese sogar noch gefunden! :100: :100: :100:
Findet man nicht mehr allzu häufig!
Deine gefundene Lösung ist - wie du ja selbst schon erkannt hast - noch nicht perfekt, da noch immer recht viel manuell gemacht werden muss. Daher schlage ich dir noch folgende Lösungsvarianten vor:
1) SVERWEIS
In L2 die Formel
Code:
=SVERWEIS(K2;'[BEISPIEL(6).xlsx]BEISPIEL'!$A$2:$B$7;2;0)
runterziehen und danach die komplette Spalte kopieren und als feste Werte einfügen (Einfügen - Inhalte einfügen - Werte), da du ja vermutlich nicht beide Dateien an deine Kunden schickst.
2) Mit VBA
Code:
Sub MWSt_Satz()
Dim i As Long
Dim MWSt As Double
With Worksheets("LÖSUNG")
For i = 2 To .Cells(Rows.Count, 11).End(xlUp).Row
Select Case .Cells(i, 11).Value
Case 56: MWSt = 0.19
Case 67: MWSt = 0.16
Case 11, 101: MWSt = 0.05
Case 19: MWSt = 0.07
Case 97: MWSt = 0
End Select
.Cells(i, 12) = MWSt
.Cells(i, 12).Style = "Percent"
Next i
End With
End Sub
Ist halt noch etwas bequemer und benötigt jedesmal nur einen Knopfdruck. Falls du überhaupt die Steuerkennzeichen überschreiben willst durch die Prozentwerte, dann ersetze
jeweils durch
Hallo,
ich habe den folgenden Code gemacht mit VBA:
Sub Thomas_Müller()
'
' Thomas_Müller Makro
'
' Tastenkombination: Strg+Umschalt+A
'
Cells.Replace What:="78", Replacement:="16 %", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="76", Replacement:="19 %", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="55", Replacement:="7 %", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="53", Replacement:="5 %", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="Steuerkennzeichen", Replacement:="Steuersatz", LookAt _
:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False _
, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub
Mein Problem: Es werden die Steuerkennzeichen zwar ersetzt, aber auch die Mengen von den Artikeln.
Kann man das so einrichten, dass der Code nur in der Spalte mit dem Spaltennamen Steuerkennzeichen funktioniert? - Weil sonst meine Mengen nicht mehr stimmen und auch durch prozentsätze ersetzt werden?
Danke
Probblem ist auch, dass die Spalten, wie beschrieben, immer an anderer stelle sind, sodass ich nicht mehr weiter weiß
Ich brauche einen Code. der die spalte "Steuerkennzeichen" sucht und markiert und dann kann ich suchen und ersetzen in genau der spalte...
Hallo,
für Suchen/Ersetzen müsstest du die jeweilige Spalte markieren und die Änderung auf den ausgewählten Bereich eingrenzen. Da ist mein Code doch bequemer:
Code:
Sub MWSt_Satz()
Dim i As Long, Spalte As Long, Suche As Range
Dim MWSt As Double
With Worksheets("LÖSUNG")
Set Suche = .Cells.Find(What:="Steuerkennzeichen", LookAt:=xlWhole)
Spalte = Suche.Column
For i = 2 To .Cells(Rows.Count, Spalte).End(xlUp).Row
Select Case .Cells(i, Spalte).Value
Case 56: MWSt = 0.19
Case 67: MWSt = 0.16
Case 11, 101: MWSt = 0.05
Case 19: MWSt = 0.07
Case 97: MWSt = 0
End Select
.Cells(i, Spalte) = MWSt
.Cells(i, Spalte).Style = "Percent"
Next i
End With
End Sub
Ich kriege leider eine fehlermeldung. Was kann ich hier am besten tun?
Hallöchen,
der Suchbegriff wird nicht vorhanden sein.
Nimm eine Zeile mehr
Set Suche = .Cells.Find(What:="Steuerkennzeichen", LookAt:=xlWhole)
If Suche Is Nothing Then MsgBox "Nix gefunden": Exit Sub
Spalte = Suche.Column
Ich verstehe die Welt nicht mehr, das funktioniert. Unsere FIrma geht doch nicht den Bach runter und dank Clever Excel Forum und dessen Genies!
Ihr beide seid richtige Genies!
@André: Das ist glaube ich meine liebste Zeile in VBA, die ich regelmäßig vergesse :21:
Ihr habt mein Anliegen super verstanden, andere sind gescheitert, das zeigt, dass ihr wirklich gut seid!
(29.10.2020, 08:05)MisterBurns schrieb: [ -> ]@André: Das ist glaube ich meine liebste Zeile in VBA, die ich regelmäßig vergesse :21:
Ich glaube, VBA ist eine feine sache, die ich gerne lernen möchte. Damit gehen sachen manchmal schneller und man kann lösungen finden, die ziemlich gut sind...
Hallo,
mit Power Query ist es ganz einfach, Tabellen mit unterschiedlicher Spaltenfolge, in eine einheitliche Struktur zu bringen. Wobei sich Power Query bereits beim Import mehrerer Tabellen an den Spaltentiteln und nicht nach den Spaltennummern richtet.
Anbei ein Beispiel mit 2 Tabellen mit unterschiedlicher Spaltenfolge und einheitlicher, vorgegebener, Ausgabefolge, unter Berücksichtigung dass auch die MWSt-Sätze ausgegeben werden.
Und wer bisher weder mit VBA, noch mit Power Query zu tun hatte, lerntden Umgang mit Power Query wesentlich schneller als den Umgang mit VBA. Wobei VBA natürlich viel mächtiger ist.