Microsoft Excel

Herbers Excel/VBA-Archiv

Langsames Makro

Betrifft: Langsames Makro von: Severin
Geschrieben am: 29.10.2020 12:31:39

Ich habe ein Makro geschrieben, welches meine Zellen nach einem Buschstaben durchsucht und dann den codierten Wert in eine andere Tabelle einträgt.

Allerdings ist meine Datenmatix gross und mein Makro braucht in etwa 10 min bis die Daten verarbeitet sind.

Gibt es da nicht eine schnellere Lösung?


Set ws = ThisWorkbook.Sheets("Data")

Dim lAnfang As Long

Dim lLetzteZeile As Long

Dim Anfang As Long

Dim Zeile As Long

Dim Suchstring As String



lLetzteZeile = ws.Range("A" & rows.Count).End(xlUp).Row

Anfang = 2

SuchstringA = "M"

SuchstringI = "S"


With ws

For Zeile = Anfang To lLetzteZeile

If .Cells(Zeile, 1) <> "" Then~f~<br>
~f~ If InStr(1, Cells(Zeile, 1).Value, "M") > 0 Then~f~<br>
Sheets("Test").Cells(Zeile, 1) = Mid(.Cells(Zeile, 1), _<br>
InStr(.Cells(Zeile, 1), SuchstringA) - 4, _<br>
Len(InStr(.Cells(Zeile, 1), SuchstringA)) + 4)<br>
Else<br>
End If<br>
<br>
~f~ If InStr(1, Cells(Zeile, 1).Value, "S") > 0 Then

Sheets("Test").Cells(Zeile, 9) = Mid(.Cells(Zeile, 1), _

InStr(.Cells(Zeile, 1), SuchstringI) - 4, _

Len(InStr(.Cells(Zeile, 1), SuchstringI)) + 3)

Else

End If

End If

Next Zeile

End With


Ich danke für eine Rückmeldung.


Liebe Grüsse

Betrifft: AW: in Array (Beipieldatei)
von: Fennek
Geschrieben am: 29.10.2020 13:20:34

Hallo,

wenn die Prüfung in einem Array vorgenommen wird, sollte es nicht länger als ein paar Sekunden dauern.

Zeige bitte 3-5 Beispieltexte aus "Data" und das Wunschergebnis aus "Test".

mfg

Betrifft: AW: in Array (Beipieldatei)
von: Severin
Geschrieben am: 29.10.2020 14:58:47

https://www.herber.de/bbs/user/141170.xlsx

Ich danke für die schnelle Antwort. Ich habe eine Beispielsdatei erstellt. Dabei werden mit einer Excel-Formel die Zeilen in Data!A:A nach einem Buchstaben durchsucht und dann, bei einem M die Zeihen vor dem M in die Tabelle Test!A:A eingefügt. Falls ein S gefunden wird, wird das gleiche mit der Spalte E gemacht. Als Formel funktioniert dies hervorragend. Leider manipuliere ich die Raw-Daten über das Makro und verliere jeweils den Bezug auf die neu erstellte Tabelle. Daher würde ich gerne diesen Prozess mit dem Makro automatisieren.

Liebe Grüsse

Betrifft: AW: Langsames Makro
von: Nepumuk
Geschrieben am: 29.10.2020 13:23:08

Hallo Severin,

kannst du den Code nochmal ohne und ~f~<br> posten?

Gruß
Nepumuk

Betrifft: AW: Langsames Makro
von: Severin
Geschrieben am: 29.10.2020 15:02:28

Set ws = ThisWorkbook.Sheets("Data")

Dim lAnfang As Long

Dim lLetzteZeile As Long

Dim Anfang As Long

Dim Zeile As Long

Dim Suchstring As String



lLetzteZeile = ws.Range("A" & rows.Count).End(xlUp).Row

Anfang = 2

SuchstringA = "M"

SuchstringI = "S"



With ws

For Zeile = Anfang To lLetzteZeile

If .Cells(Zeile, 1) <> "" Then
If InStr(1, Cells(Zeile, 1).Value, "M") > 0 Then
Sheets("Test").Cells(Zeile, 1) = Mid(.Cells(Zeile, 1), _
InStr(.Cells(Zeile, 1), SuchstringA) - 4, _
Len(InStr(.Cells(Zeile, 1), SuchstringA)) + 4)
Else
End If

If InStr(1, Cells(Zeile, 1).Value, "S") > 0 Then

Sheets("Test").Cells(Zeile, 9) = Mid(.Cells(Zeile, 1), _

InStr(.Cells(Zeile, 1), SuchstringI) - 4, _

Len(InStr(.Cells(Zeile, 1), SuchstringI)) + 3)

Else

End If

End If

Next Zeile

End With

Ich danke für die Hilfe.

Betrifft: AW: Langsames Makro
von: Daniel
Geschrieben am: 29.10.2020 14:13:11

HI
kannst du das ganze im Prinzip auch per Formel lösen?
wenn ja, dann schreib dir ein Makro dass die Formel in das entsprechende Blatt einträgt und ersetze dann die Formel durch ihren Wert.
das geht sehr schnell und sollte für jemanden, der in Excel besser ist als in VBA, auch einfach zu erstellen sein.
Gruß Daniel

Betrifft: AW: Langsames Makro
von: Severin
Geschrieben am: 29.10.2020 15:05:07

Ich danke für diesen Tip. Genau, über die Formel kann ich das Problem lösen, da ich aber meine DatenTabelle jeweils neu erstellen lasse, müsste ich jedes mal die Formel wieder anpassen, da ich immer den Bezug verlieren würde.
Die Formel wär im Excel wie folgt:
=WENN(ISTZAHL(FINDEN("S";Data!A2));TEIL(Data!A2;(FINDEN("S";Data!A2)-4);5))
Diese bekomme ich allerdings nicht wirklich in das Makro rein.

Ich danke bereits jetzt für die Hilfe.

Liebe Grüsse

Betrifft: AW: Langsames Makro
von: Daniel
Geschrieben am: 29.10.2020 15:43:54

Hi
wenn du die Formel hast, läuft das ganze nach einem starren Schema.

der Grundcode ist:
With Range(Sheets(…)Cells(erste Zeile, Spalte), Sheets(…).Cells(letzte Zeile, Spalte))
    .FormulaR1C1 = "=hier der Formeltext in R1C1 und auf englisch"
    .Formula = .Value
End with
um den Formeltext für R1C1 auf englisch zu ermitteln, gehst du so vor:
Schreibe die Formel funktionsfähig in die erste benötigte Zelle. Setze dabei alle Zellbezüge, die nicht zwingend relativ sein müssen absolut und lasse nur die zwingend relativen Zellbezüge relativ (hier die Zeilennummer) (in R1C1 werden damit die Formeln kürzer und übersichtlicher)
um den VBA-Code zu ermitteln kannst du entweder:
a) das ganze mit dem Recorder aufzeichnen
b) nachträglich die Zelle markieren und im Direktfenster: ?Replace(Selection.FormulaR1C1, """", """""")
eingeben, damit bekommst du die Formel kopierfertig inklusive der gedoppelten Anführungszeichen und kannst sie vom Direktfenster in den Code kopieren.

R1C1 und englisch ist zwar ungewohnt, aber für die Programmierung einfach besser.
A1 Addressierungsart und deutsch würde auch gehen (.FormulaLocal anstelle von .FormulaR1C1), ist aber eher unpraktisch in der Programmierung. in englisch läuft der Code dann auf jeder Länderversion und Formeln in R1C1 sind bei relativen Bezügen unabhängig von der Zielzelle, dh egal in welche Zelle du diese Formel schreibst, sie ändert sich nicht. Bei A1-Adressen müsstest du die Bezüge immer in Abhängigkeit von der Zielzelle anpassen.

solltest du Variablen innerhalb der Formel benötigen, macht man das am besten so:
1. man schreibt den Formeltext zunächst in eine Variable und schreibt hierbei anstelle des variablen Teils einen Dummy-Text, der an anderer Stelle der Formel nicht vorkommen kann.
2. man erstetzt dann den Dummy-Text mit Replace durch den Variablenwert, bevor man die Formel in die Zelle schreibt.

sieht als Code in etwa so aus, ich machs mal entgegen dem zuvor gesagten auf deutsch mit A1-Formeln, weil ich dann deine Formel einfach kopieren kann, aber ich will dir hier nur das Prinzip zeigen, mit R1C1 funktioniert das genauso:
dim FO as string
dim SuchstringI as string
SuchstringI = "S"
FO = "=WENN(ISTZAHL(FINDEN(""_xxx_"";Data!A2));TEIL(Data!A2;(FINDEN(""_xxx_"";Data!A2)-4);5))"
FO = Replace(FO, "_xxx_", SuchstringI
Range(…).FormulaLocal = FO

ist zwar ein bisschen mehr Code, aber deutlich übersichtlicher als die Variable direkt in den String mit Verketten einzubauen.

Gruß Daniel

Betrifft: AW: Langsames Makro
von: Severin
Geschrieben am: 29.10.2020 15:56:29

Lieber Daniel

Mein Kopf raucht jetzt zwar etwas, aber dies ist doch wirklich mal eine ausführliche Antwort.
Danke Dir von Herzen, habe den Code mal geschrieben und er läuft einbahnfrei.
Ich werde jetzt noch die R1C1 Version aufbauen, damit ich auch gleich diesen Teil richtig lernen kann.
Danke.

Liebe Grüsse

Severin

Betrifft: AW: Langsames Makro
von: Daniel
Geschrieben am: 29.10.2020 16:05:00

Hi
Danke für die Rückmeldung.
dieses Schema solltest du dir gut merken, denn alles was sich per Formel berechnen lässt, kann man auf diese weise einfach und schnell in VBA-Code umsetzen (der dann auch noch schnell läuft)
so kann man auch als Programmierlaie mit ein bisschen Excel-Know-how mit wenig Aufwand schnelle und leistungsfähige Makros schreiben.

und wie gesagt, R1C1 ist einfach praktischer.
Stell dir vor du stellst fest, das du noch eine zusätzliche Überschriftenzeile brauchst und die Daten jetzt in Zeile 3 beginnen.
mit einer Formel mit A1-Bezügen musst du jetzt alle relativen Bezüge auf Zeile 2 ("A2" in deiner Formel) suchen und durch 3 ersetzen.
hast du die Formel in R1C1 geschrieben, bleibt die Formel unverändert.

Gruß Daniel