需要帮助改善我的VBA循环 [英] Need help improving my VBA loop

问题描述

我有一个由两列组成的Excel工作表，其中一列填充字符串，另一列是emtpy。我想使用VBA根据其他列中的相邻字符串的值分配空列中单元格的值。

我有以下代码：

  Dim regexAdmin As Object 
 Set regexAdmin = CreateObject（VBScript.RegExp）
 regexAdmin。 IgnoreCase = True 
 regexAdmin.Pattern =Admin
 $ b $ Dim i As Integer 
 For i = 1 To 10'假设有10行
 Dim j As整数
对于j = 1到2 
如果regexAdmin.test（Cells（i，j）.Value）Then 
 Cells（i，j + 1）.Value =Exploitation
结束如果
下一个j 
下一个我
  

问题是，使用这个循环来处理大量的数据，工作时间太长，而且大部分时间，它只是崩溃的Excel。

任何人都知道更好的方法这是什么？

解决方案

你有一个不必要的循环，列（j）也是如此。删除应该提高10-50％的速度

pre $ Dim regexAdmin As Object
Set regexAdmin = CreateObject（VBScript .RegExp）
regexAdmin.IgnoreCase = True
regexAdmin.Pattern =Admin
$ b $ Dim i As Integer
For i = 1 To 10'是10行
如果regexAdmin.test（Cells（i，1）.Value）Then
Cells（i，1）.offset（0,1）.Value =Exploitation
End如果
下一个我


如果正则表达式模式真的只是管理员，那么你可以也只是使用工作表公式，而不是写一个宏。这个公式放在文本列的旁边（假设你的字符串/数字列是A）：

  = IF（NOT（ISERR（FIND（Admin，A1））），Exploitation，）
  

一般来说，如果可以用一个公式来完成，那么你最好这样做。这很容易维护。

I have an Excel Worksheet consisting of two columns, one of which is filled with strings and the other is emtpy. I would like to use VBA to assign the value of the cells in the empty column based on the value of the adjacent string in the other column.

I have the following code:

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

The problem is that when using this loop for a big amount of data, it takes way too long to work and, most of the time, it simply crashes Excel.

Anyone knows a better way to this?

解决方案

You have an unnecessary loop, where you test the just completed column (j) too. Dropping that should improve the speed by 10-50%

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
        If regexAdmin.test(Cells(i, 1).Value) Then
            Cells(i, 1).offset(0,1).Value = "Exploitation"
        End If
Next i

If the regex pattern really is simply "Admin", then you could also just use a worksheet formula for this, instead of writing a macro. The formula, which you'd place next to the text column (assuming your string/num col is A) would be:

=IF(NOT(ISERR(FIND("Admin",A1))),"Exploitation","")

In general, if it can be done with a formula, then you'd be better off doing it so. it's easier to maintain.

这篇关于需要帮助改善我的VBA循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！