excel 需要改进低效的宏(公式计算和查找/替换速度慢)

3phpmpom 于 2023-02-25 发布在其他

关注(0)|答案(1)|浏览(157)

我有两个列几乎相同的不同文件，其中一个比另一个大得多（一个大约是20K行，另一个大约是180K行），虽然，宏在较大的一个上运行得不那么好。所以这让我相信我的代码效率很低。具体来说，当我逐行步入每一行时，似乎花费最长时间的部分是“查找和替换”，和式R1Cl加成物。
我读到过使用数组可以提高效率的文章，但是我很难找到实现它们的最佳方法。
具体来说，我尝试从字符串中删除美元符号的这一部分，似乎比仅仅使用sheet.range.replace（xxx，xx，xx，）要花更长的时间。

'Initial code to remove $ from range  
Dim ws As Worksheet
Dim lrow As Long
lrow = ws.Cells(Rows.Count, 4).End(xlUp).Row
ws.Range("BG:BG, BI:BI").Replace what:="$", Replacement:="", lookat:=xlPart

'Attempt at improving speed
Dim Array1 As Variant
Set Array1 = ws.Range("BG2:BG" & lrow)
    For Each cell In Array1
        cell.value = Replace(cell.value, "$", "")
    Next cell
Dim Array2 As Variant
Set Array2 = ws.Range("BI2:BI" & lrow)
    For Each cell In Array2
        cell.value = Replace(cell.value, "$", "")
    Next cell

我还想知道我是否应该尝试添加几个不同的计算，如下面的循环通过数组，而不是直接打印到工作表。这些只是一些例子的公式使用。

ws.Range("B2:B" & lrow).FormulaR1C1 = "=rc[-1]&""-""&rc[1]"
ws.Range("P2:P" & lrow).FormulaR1C1 = "=rc[1]+rc[2]+rc[3]"
ws.Range("V2:V" & lrow).FormulaR1C1 = "=if(rc[-1]=0,0,rc[-10]/rc[-1])"
ws.Range("AY2:AY" & lrow).FormulaR1C1 = "=rc[-1]*rc[-39]"
wsnetwork.Range("B2:B" & lrow2).FormulaR1C1 = "=XLOOKUP(RC[-1],'Base'!C4,'Base'!C11)"
wsnetwork.Range("D2:D" & lrow2).FormulaR1C1 = "=if(RC[-2] = 0 , RC[-1] , RC[-2])"
wsnetwork.Range("I2:I" & lrow2).FormulaR1C1 = "=iferror(RC[-3] / RC[-1], 0)"

excel

来源：https://stackoverflow.com/questions/75550446/need-to-improve-inefficient-macro-slow-formula-calculations-and-find-replace

1条答案

按热度按时间

iswrvxsc1#

为了提高效率，你需要将数据读入数组，然后在数组中执行操作，再将数组回填到range中，就像下面这样。

Dim ws As Worksheet

data = ws.Range("BG1:BG" & ws.UsedRange.Rows.Count).Value

for i = 2 to ubound(data, 1)
    data(i, 1) = Replace(data(i, 1), "$", "")
next i

ws.Range("BG1:BG" & ws.UsedRange.Rows.Count) = data

data = ws.Range("BI1:BI" & ws.UsedRange.Rows.Count).Value

for i = 2 to ubound(data, 1)
    data(i, 1) = Replace(data(i, 1), "$", "")
next i

ws.Range("BI1:BI" & ws.UsedRange.Rows.Count) = data

赞(0）回复(0）举报 2023-02-25

我来回答

excel 需要改进低效的宏(公式计算和查找/替换速度慢)

1条答案

相关问题

热门标签

最新问答