5
Werte anhand einer Ersetzungstabelle austauschen
Hallo Leute,
ich habe folgende Problemstellung:
Ich habe eine Liste mit Artikelnummern, welche am besten auf Knopfdruck anhand einer 2. Tabelle (im Moment ein 2. Registerblatt in Excel) ausgetauscht werden sollen.
Also z.B.:
Registerblatt 1:
Art.Nr: Menge:
35 10
38 10
Registerblatt 2:
Art.Nr Kunde: Art Nr. Intern:
35 16780
38 19780
Nachdem Knopfdruck sollte dann auf Registerblatt eins folgendes stehen:
Art.Nr: Menge:
16780 10
19780 10
Die Art.Nr. sind eindeutig zuweisbar.
D.h. die Funtkion/das Skript müsste auf dem zweiten Registerblatt nach der ArtNr suchen und diese dann austauschen.
Ich habe leider wenig bis keine Erfahrung mit vba, aber ich denke für einen Erfahrenen dürfte dass doch kein Problem darstellen oder?
Vielen Dank schon im voraus
ich habe folgende Problemstellung:
Ich habe eine Liste mit Artikelnummern, welche am besten auf Knopfdruck anhand einer 2. Tabelle (im Moment ein 2. Registerblatt in Excel) ausgetauscht werden sollen.
Also z.B.:
Registerblatt 1:
Art.Nr: Menge:
35 10
38 10
Registerblatt 2:
Art.Nr Kunde: Art Nr. Intern:
35 16780
38 19780
Nachdem Knopfdruck sollte dann auf Registerblatt eins folgendes stehen:
Art.Nr: Menge:
16780 10
19780 10
Die Art.Nr. sind eindeutig zuweisbar.
D.h. die Funtkion/das Skript müsste auf dem zweiten Registerblatt nach der ArtNr suchen und diese dann austauschen.
Ich habe leider wenig bis keine Erfahrung mit vba, aber ich denke für einen Erfahrenen dürfte dass doch kein Problem darstellen oder?
Vielen Dank schon im voraus
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 132060
Url: https://administrator.de/contentid/132060
Printed on: April 19, 2024 at 01:04 o'clock
5 Comments
Latest comment
Moin bruzzi,
versteh ich nicht ganz, eure Arbeitsweise...
Ich meine, okay, dass ihr "mit einer Internen und einer externen ArtikelNr" arbeitet, machen einige ja so.
Aber warum willst du die eingegebenen ersetzen?
Könnten doch ruhig beide auf dem Bestell/Rechnungs/Lieferschein-Beleg stehen.
Oder bei euch in der echten Excel-Tabelle beim Editieren halt beide ArtNrn, bei der gedruckten oder exportierten Kunden-Version nur die "externe".
Dann reduziert sich doch die Mimik auf einen simplen SVerweis().
Grüße
Biber
versteh ich nicht ganz, eure Arbeitsweise...
Ich meine, okay, dass ihr "mit einer Internen und einer externen ArtikelNr" arbeitet, machen einige ja so.
Aber warum willst du die eingegebenen ersetzen?
Könnten doch ruhig beide auf dem Bestell/Rechnungs/Lieferschein-Beleg stehen.
Oder bei euch in der echten Excel-Tabelle beim Editieren halt beide ArtNrn, bei der gedruckten oder exportierten Kunden-Version nur die "externe".
Dann reduziert sich doch die Mimik auf einen simplen SVerweis().
Grüße
Biber
Gute Idee habe es jetzt vorläufig mit SVerweis gelöst. Aber finde ich auf Dauer unschön. Da früher oder Später noch eine Export Funktion ins ERP System folgen soll.
Gibt es auch einen vba Ansatz für so etwas. Evtl. auch mit Erstellung einer Neuen Datei?
Gibt es auch einen vba Ansatz für so etwas. Evtl. auch mit Erstellung einer Neuen Datei?
Moin bruzzi,
klar ließe sich auch mit VBA was zusammentrümmern.
Aber dennoch - das ist ja nicht das Arbeiten mit Tabellen, wie die RedmonderInnen sich das gewünscht haben.
Das würde ja auf eine "Autokorrektur"-Funktion wie in einer Textverarbeitung herauslaufen.. du tippst "mfg" und on-the-fly wird "Mit freundlichen Grüßen" daraus.
Was spricht denn gegen ein Tabellenblatt(intern), in das ihr erfasst und beide ArtNrn seht und ein Tabellenblatt für Kunde, zum Export oder zum Drucken mit nur der externen ArtNr, die über SVerweis() ermittelt wird?
Grüße
Biber
klar ließe sich auch mit VBA was zusammentrümmern.
Aber dennoch - das ist ja nicht das Arbeiten mit Tabellen, wie die RedmonderInnen sich das gewünscht haben.
Das würde ja auf eine "Autokorrektur"-Funktion wie in einer Textverarbeitung herauslaufen.. du tippst "mfg" und on-the-fly wird "Mit freundlichen Grüßen" daraus.
Was spricht denn gegen ein Tabellenblatt(intern), in das ihr erfasst und beide ArtNrn seht und ein Tabellenblatt für Kunde, zum Export oder zum Drucken mit nur der externen ArtNr, die über SVerweis() ermittelt wird?
Grüße
Biber
Naja es ist eben so, dass eine Bestellung eines Kunden kommt, der aber leider aufgrund seines Systems eben seine Artikelnummern in dieser Bestellungs Datei sendet. (ist eine CSV Datei). D.h. um diese Bestellnummern in unser ERP System zu bekommen ist eben eine Umwandlung nötig, da diese Bestellnummern nicht mit unseren Konform sind. (Und nein der Kunde kann die Nummern nicht anders senden.) Im Moment löse ich es eben so, wie vorgeschlagen, dass nach Import automatisch die Nummern ausgetauscht werden mit SVerweis. Es müssen nun eben per Hand die Daten in unser System gekloppt werden. Nun hat eben die Datei danach ein anderes Format, da ein Spalte mehr da ist. Ein Austausch der gesendeten Artikelnummern wäre eben geschickter auch zum Export.
Ich hoffe, dass ist nun einigermaßen verständlich verfasst.
Ich hoffe, dass ist nun einigermaßen verständlich verfasst.
Hallo bruzzi,
Das lässt sich ohne Probleme mit einem Programm lösen. Natürlich nur, wenn du dich darauf einlassen möchtest ein wenig Code zu lesen. Dies lohnt sich aber nur im grossen Rahmen doch dann werden kleine Programme um so mächtiger. Jeöfter man Repetition vorweisen kann.
Also,
1. wir schreiben beide Nummern aus beiden Listen in eine neue Liste
2. , die wir später als CSV exportieren und benutzerdefinert abspeichern
Nur mal eine Frage zu deinem Dateihandling.
Möchtest du jede einzelne Bestellung aus dem externen Kunden per Hand öffnen? Oder kann man theoretisch alle Bestellungen extern eines Tages in einen Sack packen und dann alle zu internen Bestellungen nach aussen hin umwandeln? Bekommst du die Kundenbestellung auch in csv? Denn man sollte das ganze direkt aus einer höheren Sichtweise betrachten, denn eventuell brauch zwischen Kundenbestellung und der Eingabe ins ERP-System oder der internen Bestellung, falls die Kundenbestellungen eine geringe Fehleranzahl haben, gar keine menschliche Excel Schnitstelle entstehen, mit sverweis und so nem Blödsinn. Das geht aber nur wenn Bestellung und ähnliches immer gleich aussieht und die gleichen Werte liefert. Für die Excel jedes einzelne Element einzeln öffnen und dann lamen habe ich den Code unten parat gestellt. einfach Alt+F11 in Excel und dann eines neues Modul hinzufügen. Den Code in dieses Modul musst du aber vorher noch auf deine Excel Datei individualisieren bezüglich Tabellenblätternamen und Zeilen- oder Spaltenfolge. Ich habe mal die Schleifendurchläufe auf 30000 beschränkt aber wenn du pro Kundenbestellung weniger hast dann kann man dass auch auf 1000 beschränken wegen dem vielleicht möglichen Überlauf der immer abgefangen werden muss. Ich fange auf sheet1 mir der zweiten Reihe an die Schleife laufen zu lassen. Siehe i = 2. Kann man ändern indem man i verändert. i2 is tdementsprechend für Tabellenblatt 2. Bei der 2. Funktion nach dem zweiten Loop wird die Datei exportiert. Das Makro sollte auf dem Blatt ausgeführt werden, welches später exportiert werden soll. Ansonste muss man ein festes Blatt festlegen mit einem jeweiligen Namen.
VBA sieht wie folgt aus:
Ich hoffe das hilft.
[Edit Biber] Codetags nachgetragen. [/Edit]
Das lässt sich ohne Probleme mit einem Programm lösen. Natürlich nur, wenn du dich darauf einlassen möchtest ein wenig Code zu lesen. Dies lohnt sich aber nur im grossen Rahmen doch dann werden kleine Programme um so mächtiger. Jeöfter man Repetition vorweisen kann.
Also,
1. wir schreiben beide Nummern aus beiden Listen in eine neue Liste
2. , die wir später als CSV exportieren und benutzerdefinert abspeichern
Nur mal eine Frage zu deinem Dateihandling.
Möchtest du jede einzelne Bestellung aus dem externen Kunden per Hand öffnen? Oder kann man theoretisch alle Bestellungen extern eines Tages in einen Sack packen und dann alle zu internen Bestellungen nach aussen hin umwandeln? Bekommst du die Kundenbestellung auch in csv? Denn man sollte das ganze direkt aus einer höheren Sichtweise betrachten, denn eventuell brauch zwischen Kundenbestellung und der Eingabe ins ERP-System oder der internen Bestellung, falls die Kundenbestellungen eine geringe Fehleranzahl haben, gar keine menschliche Excel Schnitstelle entstehen, mit sverweis und so nem Blödsinn. Das geht aber nur wenn Bestellung und ähnliches immer gleich aussieht und die gleichen Werte liefert. Für die Excel jedes einzelne Element einzeln öffnen und dann lamen habe ich den Code unten parat gestellt. einfach Alt+F11 in Excel und dann eines neues Modul hinzufügen. Den Code in dieses Modul musst du aber vorher noch auf deine Excel Datei individualisieren bezüglich Tabellenblätternamen und Zeilen- oder Spaltenfolge. Ich habe mal die Schleifendurchläufe auf 30000 beschränkt aber wenn du pro Kundenbestellung weniger hast dann kann man dass auch auf 1000 beschränken wegen dem vielleicht möglichen Überlauf der immer abgefangen werden muss. Ich fange auf sheet1 mir der zweiten Reihe an die Schleife laufen zu lassen. Siehe i = 2. Kann man ändern indem man i verändert. i2 is tdementsprechend für Tabellenblatt 2. Bei der 2. Funktion nach dem zweiten Loop wird die Datei exportiert. Das Makro sollte auf dem Blatt ausgeführt werden, welches später exportiert werden soll. Ansonste muss man ein festes Blatt festlegen mit einem jeweiligen Namen.
VBA sieht wie folgt aus:
Sub umwandelexport
dim i as integer
dim artnrkunde
dim artnrintern
dim n as string
dim sheet1 as string
dim i2 as integer
dim sheet2 as string
Dim strAlterName As String
n = ActiveWorkbook.name
sheet1 = "Name des Tabellenblattes 1 in Anführungszeichen"
sheet2 = "Name des Tabellenblattes 1 in Anführungszeichen"
i = 2
i2 = 2
Do Until Workbooks(n).Sheets(sheet1).Cells(i,1).Value = "" Or i = 30000
artnrkunde = Workbooks(n).Sheets(sheet1).Cells(i,1).Value
Do Until Workbooks(n).Sheets(sheet2).Cells(i2,1).Value = artnrkunde Or _
Workbooks(n).Sheets(sheet2).Cells(i2,1).Value = "" Or i2 >10000
i2 = i2 +1
Loop
If Workbooks(n).Sheets(sheet2).Cells(i2,1).Value = artnrkunde Then
artnrintern = Workbooks(n).Sheets(sheet2).Cells(i2,1).Value
Workbooks(n).Sheets(sheet).Cells(i,1).Value = artintern
End if
i = i + 1
Loop
strAlterName = Me.Name 'Blattnamen merken
ChDrive ActiveWorkbook.Path 'Arbeitsverzeichnis auf Verzeichnis, in dem Excel- Datei liegt
ChDir ActiveWorkbook.Path
On Error GoTo fehler
ActiveWorkbook.SaveAs FileFormat:=xlCSV, CreateBackup:=False
Sheets(1).Name = strAlterName 'alten Namen wiederherstellen, weil dieser durch Speichern als CSV geändert
ActiveWorkbook.SaveAs FileFormat:=xlWorkbookNormal 'nur, um das Workbook wieder als xls zu kriegen
Exit Sub
fehler:
If Err Then MsgBox "Datei wurde nicht gespeichert"
Sheets(2).Name = strAlterName
End Sub [Edit Biber] Codetags nachgetragen. [/Edit]
