Zuweisen von Werten zu Zellen mit Excel VBA

279932
user50726

Ich habe ein Excel-Arbeitsblatt, das aus zwei Spalten besteht, von denen eine mit Zeichenfolgen gefüllt ist und die andere leer ist. Ich möchte VBA verwenden, um den Wert der Zellen in der leeren Spalte basierend auf dem Wert der benachbarten Zeichenfolge in der anderen Spalte zuzuweisen.

Dim regexAdmin As Object 
Set regexAdmin = CreateObject("VBScript.RegExp") 
regexAdmin.IgnoreCase = True
regexAdmin.Pattern = "Admin" 

Dim i As Integer
For i = 1 To 10 'let's say there is 10 rows
    Dim j As Integer
    For j = 1 To 2
        If regexAdmin.test(Cells(i, j).Value) Then
            Cells(i, j + 1).Value = "Exploitation"
        End If
    Next j
Next i

Das Problem ist, dass die Verwendung dieser Schleife für eine große Datenmenge viel zu lange dauert und meistens einfach Excel abstürzt.

Weiß jemand einen besseren Weg, dies zu tun?

Antworten
14

6 Antworten auf die Frage

11
RubberDuck

Die kurze Antwort lautet:

Verwenden Sie keine , verwenden Sie eine Formel. Insbesondere eine Kombination aus IFund SUCHEN .

=IF(SEARCH($A1,"Admin")>0,"Exploitation","")

Aber das ist Code-Review, also machen wir das trotzdem.

  • Regex ist langsam. Es scheint, dass Sie es nur wegen der Unempfindlichkeit gegen Groß- und Kleinschreibung verwenden. Aus diesem Grund können Sie Zellwerte direkt StrCompmit der vbTextCompareOption vergleichen. ( nützlicher Artikel zu StrComp )

  • iund j werden typischerweise für Schleifenzähler verwendet, aber rowund colmehr Sinn in diesem Fall machen.

So könnte dies aussehen:

Dim row As Integer
For row = 1 To 10 'let's say there is 10 rows
    Dim col As Integer
    For col = 1 To 2
        If StrComp("Admin",Cells(row, col).Value,vbTextCompare) Then
            Cells(row, col + 1).Value = "Exploitation"
        End If
    Next col
Next row
7
PerryJ

Ich denke, dass ein einfacher Stringvergleich viel schneller als Regex wäre.

Dim pattern as string
pattern = "Admin"
Dim i As Integer
For i = 1 To 10 'let's say there is 10 rows
    Dim j As Integer
    For j = 1 To 2
        If Cells(i, j) = pattern Then
            Cells(i, j + 1) = "Exploitation"
        End If
    Next j
Next i
Mit diesem Code wird der Vergleich der Groß- und Kleinschreibung nicht berücksichtigt, aber Sie haben Recht. Der Regex ist Overkill und in diesem Fall wird der Vergleich von Strings bevorzugt. (Willkommen bei Code Review übrigens!) RubberDuck vor 5 Jahren 0
7
user1016274

Kann ich eine Reduzierung der Laufzeit / des Aufwandes um 50% vorschlagen?

Dim row As Integer
For row = 1 To 10 'let's say there is 10 rows
    If StrComp("Admin",Cells(row, 1).Value,vbTextCompare) Then
        Cells(row, 2).Value = "Exploitation"
    End If
Next row

Hat niemand gemerkt, dass das OP wirklich davon spricht, "ONE Kolumne zu prüfen, in die NEXT nebeneinander zu schreiben"? Warum dann Spaltenspalten? Beim zweiten Durchlauf wird nur eine leere Zelle oder eine Zelle mit "Verwertung" geprüft.

6
Mathieu Guindon

Beeindruckend. Beim Lesen der ersten Zeilen habe ich mich gefragt:

  • Warum die späte Bindung?
  • Warum überhaupt einen Regex verwenden?

@ ckuhn203 hat bereits die Namensgebung in seiner Antwort angesprochen, aber ich finde dies:

Dim i As Integer
For i = 1 To 10 'let's say there is 10 rows

Daraus gemacht:

Dim row As Integer
For row = 1 To 10 'let's say there is 10 rows

... braucht den Kommentar nicht mehr.


Ich möchte VBA verwenden, um den Wert der Zellen in der leeren Spalte basierend auf dem Wert der benachbarten Zeichenfolge in der anderen Spalte zuzuweisen.

Ich denke, das ist [mis | ab] mit VBA: Excel selbst ist sehr gut darin, Zellenwerte basierend auf den Werten anderer Zellen zuzuweisen.


regexAdmin.Pattern = "Admin" 

Ich denke, das ist [mis | ab] mit Regex: Wenn Ihr Muster nur ein einfaches Wort ist, versuchen Sie höchstwahrscheinlich, eine Mücke mit einer Bazooka zu töten. Falsches Werkzeug für den Job hier.

6
Luke Cummings

Wenn Sie auf das Range-Objekt zugreifen, sollte dies mit einer einzigen Lese- / Schreiboperation erfolgen.

Bevor Sie die for-Schleife betreten, sollten Sie den gesamten Bereich lesen, mit dem Sie arbeiten möchten.

data = Range(Cells(1,1), Cells(10,2)).Value

Jetzt können Sie mit den Daten arbeiten:

For i = 1 To 10 'let's say there is 10 rows
    Dim j As Integer
    For j = 1 To 2
        If regexAdmin.test(data(i, j)) Then
            data(i, j + 1) = "Exploitation"
        End If
    Next j
Next i

Zum Schluss schreibe die Daten zurück in Excel:

Range(Cells(1,1), Cells(10,2)).Value = data
5
Gary Ritter

In B1:

=if(upper(A1)="ADMIN","Exploitation","")

Dann füllen Sie es einfach aus. Dies ist unabhängig von der Groß- und Kleinschreibung.

Dieses automatische Ausfüllen kann auf zwei Arten erfolgen, entweder interaktiv im Arbeitsblatt oder programmgesteuert:

Interaktiv: Excel verfügt über eine Funktion zum automatischen Ausfüllen. Wenn Sie B1 ausgewählt und diese Formel eingegeben haben, doppelklicken Sie einfach auf den Füllpunkt. Dies ist das kleine Quadrat in der rechten unteren Ecke der Zelle, wenn es ausgewählt ist. Excel kopiert die Formel intelligent bis zum Ende des zusammenhängenden Bereichs, in dem sich Daten befinden. Das heißt, wenn A1-A256 Daten enthält, die keine Leerzeichen enthalten, werden sie automatisch nach B256 gefüllt. Alternativ können Sie bei Leerzeichen nach unten scrollen und B256 (oder was auch immer das Ende ist) auswählen. Dann Strg + Umschalt + Aufwärtspfeil, um den Bereich zu wählen, der zu B1 führt, und Strg + D, um ihn nach unten zu kopieren (denke, d = ditto)

VBA verwenden ... wenn Sie dies programmgesteuert tun müssen: mit der Formel, die Zelle als Auswahl enthält:

Selection.AutoFill Destination:=Range("B1:B19")

Es gibt andere Optionen für das automatische Füllen, um ein paar coole Tricks auszuführen. Kann einen Literalwert anstelle einer Formel kopieren oder auch eine Reihe anhand eines Musters füllen. Sie können auch benutzerdefinierte Muster festlegen, die erkannt werden sollen, z. B. Geschäftszweige, die Sie häufig in Geschäften oder Städten wiederholen, in denen Sie Einzelhandelsstandorte haben, usw.

Willkommen bei Code Review! Ich habe völlig vermisst, dass das OP die Spalte nicht durchsuchen musste! ++ Beste Lösung hier. RubberDuck vor 4 Jahren 1