Anzeige
Archiv - Navigation
1004to1008
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA Problem

VBA Problem
26.08.2008 08:09:00
Manuel
Hallo,
mal wieder stehe ich vor einem Excel VBA Problem, dass ich selbst nicht gelöst bekomme. Kann mir vielleicht jemand sagen, wie die notwendige Schleifen aussehen müssen, dass die folgenden Schritte abgearbeitet werden:
1.) Durch Klicken auf Button ("KKS_Zusammenführen") soll in WORKSHEETS("KKS ABFRAGE") die Spalte N ab Zeile 3 bis Zeile i (i = Wert aus Zelle B2) durchlaufen werden, und dabei soll jeder Wert in der Spalte N (teilweise leere Zeilen) untereinander in das Sheet "KKS Zusammenführung" in die Zellen A2 bis Axxxx
Im Zuge dessen, soll für die dazugehörigen Zellen E2 bis Exxxx ein SVERWEIS-Befehl geschrieben werden, der in Excel so aussehen würde:
=SVERWEIS(VERKETTEN("MUE =";Ax);'SAP - 05-06-08 - roh'!$A$1:$B$40000;2;FALSCH)
2.) Durch Klicken auf ("MeLi_Zusammenführen") soll das Worksheet("V_MELI_ALLG") in der Spalte AE ab Zeile 3 bis i (i = Zelle i1) durchlaufen werden und es sollen wie in 1.) alle Werte der Zellen in das Tabellenblatt "KKS Abfrage" ab der ersten Leeren Zeile in Spalte A angefügt werden.
Außerdem sollen hier zwei SVERWEIS Befehle geschrieben werden:
Zellen Dx:
=SVERWEIS(VERKETTEN("MUE =";Ax);V_MELI_ALLG'!$i$3:$Y$40000;17;FALSCH)
Zellen Ex:
=SVERWEIS(VERKETTEN("MUE =";Ax);V_MELI_ALLG'!$i$3:$K$40000;3;FALSCH)
Würde mich freuen, wenn sich jemand unter euch findet, der mir bei dieser wahrscheinlich einfachen Aufgabenstellung behilflich sein könnte.
MfG
Manuel Hagel

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Problem
26.08.2008 10:52:00
Klaus
Hallo Manuel,
eine Mustertabelle uploaden währ toll gewesen, dann könnte man das gleich testen ....
so mal eben aus dem Ärmel geschüttelt und natürlich ungetestet. Die Makros den Buttons zuweisen kannst du bestimmt selbst?

'### in ein Modul
Option Explicit
Sub Aufgabe1()
Dim i As Long
Dim myRow As Long
Dim rng As Range
myRow = 2
With Sheets("KKS Abfrage")
i = .Range("B2")
For Each rng In .Range("N3:N" & i)
If rng.Value = "" Then
Else
Sheets("KSS Zusammenführung").Range("A" & myRow) = rng.Value
Sheets("KSS Zusammenführung").Range("E" & myRow).FormulaLocal = "=SVERWEIS( _
VERKETTEN(""MUE ="";Ax);'SAP - 05-06-08 - roh'!$A$1:$B$40000;2;FALSCH)"
myRow = myRow + 1
End If
Next 'rng
End With
End Sub
Sub aufgabe2()
Dim i As Long
Dim myRow As Long
Dim rng As Range
myRow = Sheets("KSS Zusammenführung").Range("A65536").End(xlUp).Row + 1
With Sheets("V_MELI_ALLG")
i = .Range("I1")
For Each rng In .Range("AE3:AE" & i)
If rng.Value = "" Then
Else
Sheets("KSS Abfrage").Range("A" & myRow) = rng.Value
Sheets("KSS Abfrage").Range("D" & myRow).FormulaLocal = "=SVERWEIS(VERKETTEN("" _
MUE ="";Ax);V_MELI_ALLG'!$i$3:$Y$40000;17;FALSCH)"
Sheets("KSS Abfrage").Range("E" & myRow).FormulaLocal = "=SVERWEIS(VERKETTEN("" _
MUE ="";Ax);V_MELI_ALLG'!$i$3:$K$40000;3;FALSCH)"
myRow = myRow + 1
End If
Next 'rng
End With
End Sub
'#####


Grüße,
Klaus M.vdT.

Anzeige
AW: VBA Problem
26.08.2008 12:09:51
Manuel
Hallo Klaus,
besten Dank für deine "aus dem Arm geschüttelte" Lösung, mit ein paar kleinen Korrekturen ging es so wunderbar - danke dir.
Gruß
Manuel
Danke für die Rückmeldung! o.w.T.
26.08.2008 13:28:09
Klaus
.
AW: VBA Problem
26.08.2008 12:18:32
fcs
Hallo Klaus, Hallo Manuel,
bei dieser Lösung muss in den Zeilen, in denen die Formeln eingetragen werden muss
Ax durch A" & myRow & "
ersetzt werden, damit der SVERWEIS korrekt funktioniert.
Beispiel:

Sheets("KSS Zusammenführung").Range("E" & myRow).FormulaLocal = _
"=SVERWEIS(VERKETTEN(""MUE ="";A" & myRow & _
");'SAP - 05-06-08 - roh'!$A$1:$B$40000;2;FALSCH)"


Gruß
Franz

Anzeige
AW: VBA Problem
26.08.2008 13:36:00
Klaus
Oh ja, danke Franz
die "x"-er Platzhalter in den Formeln hab ich doch glatt übersehen.
Grüße,
Klaus M.vdT.
AW: VBA Problem
26.08.2008 11:54:23
fcs
Hallo Manuel,
hier 2 Makros für deine beiden Buttons.
Ich bin mir nicht sicher, in welchem Tabellenblatt sich jeweils die Zellen B2 bzw. I1 zum auslesen der Zeile i für die Schleifen befinden. Hier muss du ggf. noch etwas anpassen.
Gruß
Franz

Sub KKS_Zusammenfuehren()
Dim wksAbfrage As Worksheet
Dim wksZusammen As Worksheet
Dim ZeileZusammen As Long, Zeile As Long
Set wksAbfrage = Worksheets("KKS ABFRAGE")
Set wksZusammen = Worksheets("KKS Zusammenführung")
ZeileZusammen = 2
With wksZusammen
For Zeile = 3 To wksAbfrage.Range("B2")
'Wert aus KKS_Abfrage Spalte N in KKS Zusammenführung eintragen
.Cells(ZeileZusammen, 1).Value = wksAbfrage.Cells(Zeile, 14).Value
'Formel in Spalte E einfügen
'=SVERWEIS(VERKETTEN("MUE =";Ax);'SAP - 05-06-08 - roh'!$A$1:$B$40000;2;FALSCH)
.Cells(ZeileZusammen, 5).FormulaR1C1 = _
"=VLOOKUP(CONCATENATE(""MUE ="",RC[-4]),'SAP - 05-06-08 - roh'!R1C1:R40000C2,2,FALSE)"
ZeileZusammen = ZeileZusammen + 1
Next
End With
End Sub
Sub MELI_Zusammenfuehren()
Dim wksAbfrage As Worksheet
Dim wksMeLi As Worksheet
Dim ZeileZusammen As Long, Zeile As Long
Set wksAbfrage = Worksheets("KKS ABFRAGE")
Set wksMeLi = Worksheets("V_MELI_ALLG")
With wksAbfrage
'nächste freie Zeile in Spalte A Blatt KKS_Abfrage
ZeileZusammen = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
For Zeile = 3 To wksMeLi.Range("I1")
'Wert aus V_MELI_ALLG Spalte AE in KKS ABFRAGE eintragen
.Cells(ZeileZusammen, 1).Value = wksMeLi.Cells(Zeile, 31).Value
'Formel in Spalte D einfügen
'=SVERWEIS(VERKETTEN("MUE =";Ax);'V_MELI_ALLG'!$i$3:$Y$40000;17;FALSCH)
.Cells(ZeileZusammen, 4).FormulaR1C1 = _
"=VLOOKUP(CONCATENATE(""MUE ="",RC[-3]),'V_MELI_ALLG'!R3C9:R40000C25,17,FALSE)"
'Formel in Spalte E einfügen
'=SVERWEIS(VERKETTEN("MUE =";Ax);'V_MELI_ALLG'!$i$3:$K$40000;3;FALSCH)
.Cells(ZeileZusammen, 5).FormulaR1C1 = _
"=VLOOKUP(CONCATENATE(""MUE ="",RC[-4]),'V_MELI_ALLG'!R3C9:R40000C11,3,FALSE)"
ZeileZusammen = ZeileZusammen + 1
Next
End With
End Sub


Anzeige
AW: VBA Problem
26.08.2008 13:37:00
Manuel
Hallo Franz,
auch dir nochmals danke für die Mühen. Das was du in deinem ersten Beitrag angemerkt hast, meinte ich mit kleinen Korrekturen, die ich bereits selbst korrigieren konnte.
Nachdem die Befehle der Buttons funktionierten, musste ich jedoch feststellen, dass Excel die Datei in anbetracht der rund 20000 Zeilen, die in Tabellenblatt "KKS Zusammenführen" geschrieben werden sehr aufbläst. Daher wollte ich die Funktionen nun in VBA ablaufen lassen, was mir für die Spalten B udn C (eingangs nicht erwähnt) auch gelang:
KKS = rng.Value
KKS1 = Mid(KKS, 1, 7)
Sheets("KKS Zusammenführung").Range("B" & myRow) = KKS1
KKS2 = Mid(KKS, 8, 10)
Sheets("KKS Zusammenführung").Range("C" & myRow) = KKS2
Allerdings habe ich bei den genannten Sverweisen ein Problem, und zwar wollte ich fragen ob ich auch diese in VBA einen nach dem anderen für die Zeilen 2 bis i laufen lassen kann? Leider hiflt mir hier das Makro das ich aufzeichnen konnte überhaupt nicht weiter. Es funktioniert auf diesem Weg
Sheets("KKS Zusammenführung").Range("E" & myRow).FormulaLocal = "=SVERWEIS(VERKETTEN(""MUE ="";A" & myRow & ");'SAP - 05-06-08 - roh'!$A$1:$B$40000;2;FALSCH)"
, wenn ich den SVERWEIS als Formel schreiben lasse, allerdings hängt sich dann Excel bei 20000 Zeilen irgendwo zwischendrin immer auf.
Wäre super wenn hier nochmals jemand einen Vorschlag hätte.
Anzeige
AW: VBA Problem
26.08.2008 13:54:00
Klaus
Hallo Manuel,
statt die SVERWEIS Formel 2000 mal zu schreiben (und jedes mal neu zu berechnen), schreibe sie einmalig und kopiere sie dann am ende des Makro nach unten!
Ich habe Aufgabe 1 mal angepasst, die zweite schaffst du selber ?

Sub Aufgabe1()
Dim i As Long
Dim myRow As Long
Dim oldRow As Long
Dim rng As Range
myRow = 2
oldRow = myRow
With Sheets("KKS Abfrage")
i = .Range("B2")
For Each rng In .Range("N3:N" & i)
If rng.Value = "" Then
Else
Sheets("KSS Zusammenführung").Range("A" & myRow) = rng.Value
myRow = myRow + 1
End If
Next 'rng
End With
With Sheets("KSS Zusammenführung")
.Range("E" & oldRow).FormulaLocal = "=SVERWEIS(VERKETTEN(""MUE ="";A" & oldRow & ");' _
SAP - 05-06-08 - roh'!$A$1:$B$40000;2;FALSCH)"
.Range("E" & oldRow).Copy .Range("E" & oldRow + 1 & ":E" & myRow)
End With
End Sub


eventuell kann es auch helfen, vor dem Makro die Berechnung auf "manuell" und am Schluss wieder auf "automatisch" zu schalten, dann rechnet Excel nur noch einmal vor und nach aufruf des Makros.
Grüße,
Klaus M.vdT.

Anzeige
AW: VBA Problem
26.08.2008 14:35:00
Manuel
Hallo Franz,
danke für den Rat, aber ich denke das dürfte bei der Menge der Daten keinen Unterschied geben. Mit 20000 Schreiben der Formel oder Kopieren bleibt sich gleich, d.h. Excel muss rechnen, sobald der Code alle Zeilen geschrieben hat. Meine Hoffnung war nun, dass ich den Befehl über VBA erschlagen kann, sodass Zeile für Zeile im Rahmen des Codes gerechnet werden. Allerdings scheitert es schon daran, dass der VBA Editor mir sagt, er würde den Befehl CONCATENATE (Verketten) nicht kennt (er sagt: FEHLER BEIM KOMPILIEREN: SUB ODER FUNCTION NICHT DEFINIERT)
Vorschläge?
Gruß Manuel
Anzeige
AW: VBA Problem
26.08.2008 16:37:45
Klaus
Hallo Manuel,
ich bin aber nicht Franz :-)
Vorschlag:

With Sheets("KSS Zusammenführung")
Application.Calculation = xlManual
.Range("E" & oldRow).FormulaLocal = "=SVERWEIS(VERKETTEN(""MUE ="";A" & oldRow & ");' _
SAP - 05-06-08 - roh'!$A$1:$B$40000;2;FALSCH)"
.Range("E" & oldRow).Copy .Range("E" & oldRow + 1 & ":E" & myRow)
Application.Calculation = xlAutomatic
calculate
End With


Damit gewöhnst du Excel für die Zeit des kopierens das rechnen ab!
Grüße,
Klaus M.vdT.

AW: VBA Problem
26.08.2008 20:07:18
fcs
Hallo Manuel,
wenn du "nur" das Ergebnis der SVERWEIS-Funktionen in die Zellen eintragen willst, dann muss der Code der beiden Prozeduren wie folgt aussehen. Bei mehr als 20000 Datenzeilen ist das Makro aber schon einige Zeit beschäftigt.
Alternativ kannst du auch das bisherige Makro verwenden und am Ende mit der Kopierfunktion die Formeln in den Spalten durch Werte ersetzen.
Gruß
Franz

Sub MELI_Zusammenfuehren2()
Dim wksAbfrage As Worksheet
Dim wksMeLi As Worksheet
Dim ZeileZusammen As Long, Zeile As Long, ZeileMeLiLast As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next 'überspringt Werteintrag bei Fehlerergebnis von VLookup
Set wksAbfrage = Worksheets("KKS ABFRAGE")
Set wksMeLi = Worksheets("V_MELI_ALLG")
'letzte Datenzeile in Spalte I im Blatt V_MELI_ALLG
With wksMeLi
ZeileMeLiLast = .Cells(.Rows.Count, 9).End(xlUp).Row
End With
With wksAbfrage
'nächste freie Zeile in Spalte A Blatt KKS_Abfrage
ZeileZusammen = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
For Zeile = 3 To wksMeLi.Range("I1")
'Wert aus V_MELI_ALLG Spalte AE in KKS ABFRAGE eintragen
.Cells(ZeileZusammen, 1).Value = wksMeLi.Cells(Zeile, 31).Value
'SVERWEIS-Wert in Spalte D einfügen
'=SVERWEIS(VERKETTEN("MUE =";Ax);'V_MELI_ALLG'!$i$3:$Y$40000;17;FALSCH)
.Cells(ZeileZusammen, 4).Value = Application.WorksheetFunction. _
VLookup("MUE =" & .Cells(ZeileZusammen, 1).Value, _
wksMeLi.Range("$i$3:$Y$" & ZeileMeLiLast), 17, False)
'SVERWEIS-Wert in Spalte E einfügen
'=SVERWEIS(VERKETTEN("MUE =";Ax);'V_MELI_ALLG'!$i$3:$K$40000;3;FALSCH)
.Cells(ZeileZusammen, 5).Value = Application.WorksheetFunction. _
VLookup("MUE =" & .Cells(ZeileZusammen, 1).Value, _
wksMeLi.Range("$i$3:$K$" & ZeileMeLiLast), 3, False)
ZeileZusammen = ZeileZusammen + 1
Next
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Sub KKS_Zusammenfuehren2()
Dim wksAbfrage As Worksheet
Dim wksZusammen As Worksheet
Dim wksSAP As Worksheet
Dim ZeileZusammen As Long, Zeile As Long, ZeileSAPLast As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next 'überspringt Werteintrag bei Fehlerergebnis von VLookup
Set wksAbfrage = Worksheets("KKS ABFRAGE")
Set wksZusammen = Worksheets("KKS Zusammenführung")
Set wksSAP = Worksheets("SAP - 05-06-08 - roh")
'letzte Datenzeile in Spalte A im Blatt "SAP - 05-06-08 - roh"
With wksSAP
ZeileSAPLast = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
ZeileZusammen = 2
With wksZusammen
For Zeile = 3 To wksAbfrage.Range("B2")
'Wert aus KKS_Abfrage Spalte N in KKS Zusammenführung eintragen
.Cells(ZeileZusammen, 1).Value = wksAbfrage.Cells(Zeile, 14).Value
'SVERWEIS-WERT in Spalte E einfügen
'=SVERWEIS(VERKETTEN("MUE =";Ax);'SAP - 05-06-08 - roh'!$A$1:$B$40000;2;FALSCH)
.Cells(ZeileZusammen, 5).Value = Application.WorksheetFunction. _
VLookup("MUE =" & .Cells(ZeileZusammen, 1).Value, _
wksSAP.Range("$A$1:$B$" & ZeileSAPLast), 2, False)
ZeileZusammen = ZeileZusammen + 1
Next
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige