Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Bestimmte Werte durch andere Werte ersetzen?
#1
Lightbulb 
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.


Angehängte Dateien
.xlsx   BEISPIEL.xlsx (Größe: 9,18 KB / Downloads: 10)
.xlsx   LÖSUNG.xlsx (Größe: 9,68 KB / Downloads: 10)
Antwortento top
#2
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
Code:
.Cells(i, 12)
jeweils durch
Code:
.Cells(i, 11)
Schöne Grüße
Berni
Antwortento top
#3
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...
Antwortento top
#4
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
Schöne Grüße
Berni
Antwortento top
#5
Ich kriege leider eine fehlermeldung. Was kann ich hier am besten tun?


Angehängte Dateien Thumbnail(s)
   
Antwortento top
#6
Hallöchen,

der Suchbegriff wird nicht vorhanden sein.

Nimm eine Zeile mehr Smile

Set Suche = .Cells.Find(What:="Steuerkennzeichen", LookAt:=xlWhole)
If Suche Is Nothing Then MsgBox "Nix gefunden": Exit Sub
Spalte = Suche.Column
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
Antwortento top
#7
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!
Antwortento top
#8
@André: Das ist glaube ich meine liebste Zeile in VBA, die ich regelmäßig vergesse  21
Schöne Grüße
Berni
Antwortento top
#9
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...
Antwortento top
#10
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.


Angehängte Dateien
.xlsx   cef - Bestimmte Werte durch andere Werte ersetzen (Power Query).xlsx (Größe: 34,61 KB / Downloads: 4)
VG, wisch
Wer Hilfe nimmt, sollte auch Hilfe geben! Auch wenn dies auf einem ganz anderem Gebiet geschieht.
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste