Excel VBA ile hücrelere değer atama

Biri dizelerle dolu, diğeri boş olan iki sütundan oluşan bir Excel Çalışma Sayfam var. Diğer sütundaki bitişik dizginin değerini temel alarak boş sütundaki hücrelerin değerini atamak için VBA'yı kullanmak istiyorum.

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

Sorun şu ki, bu döngüyü büyük miktarda veri için kullanırken, çalışması çok uzun sürüyor ve çoğu zaman yalnızca Excel'in çökmesine neden oluyor.

Bunu yapmanın daha iyi bir yolunu bilen var mı?

13

6 cevap

Kısa cevap:

Don't use , use a formula. In particular, a combination of IF and SEARCH.

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

Ama bu kod incelemesi, hadi yine de yapalım.

  • Regex yavaş. Öyle görünüyor ki, sadece büyük/küçük harfe duyarsızlığı için kullanıyorsunuz. Buna göre, vbTextCompare seçeneğiyle StrComp öğesini kullanarak hücre değerlerini doğrudan karşılaştırabilirsiniz. ( StrComp'ta yararlı makale )

    /li>
  • i ve j genellikle döngü sayaçları için kullanılır, ancak satır ve col bu durumda daha anlamlı olur.

İşte bunun nasıl görünebileceği:

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
11
katma

Basit string karşılaştırmasının Regex'ten çok daha hızlı olacağını düşünüyorum.

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
7
katma
Bu kod, büyük/küçük harf duyarlı olmayan karşılaştırmaya yönelik değildir, ancak haklısınız. Regex overkill ve bu durumda dize karşılaştırma tercih edilir. (Bu arada Code Review'a hoş geldiniz!)
katma yazar RubberDuck, kaynak

Çalışma süresinde/çabasında% 50 azalma önerebilir miyim?

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

Gerçekten de OP'nin "BİR sütunu kontrol etmek, NEXT'ye bitişik yazı yazmak" hakkında konuştuğunu fark etmedi mi? Neden sütunları o zaman döngü? İkinci geçiş, yalnızca boş bir hücreyi veya içinde "Sömürme" olan birini kontrol eder.

7
katma

Vay. Sadece ilk birkaç satırı okuyarak merak ettim:

  • Neden geç bağlayıcı?
  • Neden regex kullanıyorsunuz?

@ ckuhn203 zaten cevabındaki isimlendirmeyi ele aldı, ancak şunu buldum:

  Tam Sayı Olarak Dim i
İ = 1 ila 10 'için diyelim ki 10 satır var
 

Buna dönüştüm:

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

... Artık yoruma gerek yok.


Diğer sütundaki bitişik dizginin değerini temel alarak boş sütundaki hücrelerin değerini atamak için VBA'yı kullanmak istiyorum.

Bence bu [mis | ab] VBA kullanıyor: Excel'in kendisi, diğer hücrelerin değerlerine dayanarak hücre değerleri atamakta çok başarılı.


  regexAdmin.Pattern = "Yönetici"
 

Bence bu [mis | ab] regex kullanıyor: eğer kalıp sadece düz bir kelime ise, muhtemelen bir bazuka ile sivrisinek öldürmeye çalışıyorsunuzdur. Burada iş için yanlış bir araç.

6
katma

Range nesnesine her erişişinizde, tek bir okuma/yazma işlemi yapılmalıdır.

For döngüsüne girmeden önce, çalışmak istediğiniz tüm aralığı okumalısınız.

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

Şimdi verilerle çalışabilirsiniz:

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

Sonunda verileri tekrar excel'e yazın:

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

B1’de:

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

O zaman sadece doldurun. Bu büyük/küçük harf duyarsız.

Bu otomatik doldurma, çalışma sayfasında etkileşimli olarak veya programlı olarak iki şekilde yapılabilir:

Interactively: Excel has an autofill feature. With B1 selected and that formula put in, just double click the fill handle, which is the tiny square in the bottom right corner of cell when it's selected. Excel will intelligently copy the formula down to the end of the contiguous range that has data. Meaning if A1-A256 has data with no blanks, it'll autofill to B256. Alternatively, if there are blanks, scroll to the bottom and select B256 (or whatever the end is). Then Ctrl + Shift + Up arrow to select the range leading to B1, and Ctrl+D to copy it down (think d= ditto)

Using VBA... if you must do this programmatically: with the formula containing cell as your selection:

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

Otomatik doldurmanın birkaç harika numara yapması için başka seçenekler de var. Formül yerine değişmez bir değeri kopyalayabilir veya bir desene dayalı bir seriyi doldurabilir. Ayrıca, iş yerlerinde sıkça yinelemekte olduğunuz iş kollarında veya perakende satış yerleri olan şehirlerde vb. Tanımak için özel kalıplar belirleyebilirsiniz.

3
katma
Gerçekten Kod İncelemeye hoş geldiniz! OP'nin sütunu aramaya ihtiyacı olmadığını tamamen özledim! ++ Burada en iyi çözüm.
katma yazar RubberDuck, kaynak