excel - 使用公式加速 If Then 语句的 VBA 代码

Question

我目前有两个如果是 VBA 代码，它们对于我的大型数据集运行速度非常慢，并且正在寻找优化和加速它们的方法。

第一个公式在 J 列中查找在 A 列中有值的单元格区域，如果它们在 J 中为空白，则输入包含用户定义函数的公式。

第二个代码是查看 J 列中的任何值是否以 , 结尾。如果他们这样做，则删除该逗号。任何帮助将不胜感激!

Sub FillEmpty()
    Dim r As Range, LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).row
    For Each r In Range("J2:J" & LastRow)
        If r.Text = "" Then r.FormulaR1C1 = _
           "=IFERROR((IF(LEFT(RC[-9],6)=""master"", get_areas(RC[-7]), """")),"""")"
    Next r
End Sub

Sub NoComma()
    Dim c As Range
    For Each c In Range("J:J")
        With c
            If Right(.Value, 1) = "," Then .Value = Left(.Value, Len(.Value) - 1)
        End With
    Next c
End Sub

Answer 1

加速:数组

1. 代码

令人难以置信的是，从数组粘贴到范围时，您不需要 formulaR1C1 将公式放入范围。但它在我的电脑上运行。总而言之，第二个代码的相同原理适用于第一个代码: Range into Array, Loop 和 Array into Range .没有比这更快的了。第一个代码的另一个想法是创建一个范围联合，然后一次性粘贴公式。

Sub FillEmpty()

  Const cCol As Variant = "J"      ' Column Letter/Number
  Const cFirst As Long = 2         ' First Row

  Dim vntFE As Variant             ' Range Array
  Dim i As Long                    ' Range Array Rows Counter

  ' Paste range into array.
  vntFE = Cells(cFirst, cCol).Resize(Cells(Rows.Count, cCol) _
      .End(xlUp).Row - cFirst + 1)

  ' Loop through array and perform calculation.
  For i = 1 To UBound(vntFE)
    If vntFE(i, 1) = "" Then vntFE(i, 1) = "=IFERROR((IF(LEFT(RC[-9],6)" _
        & "=""master"", get_areas(RC[-7]), """")),"""")"
  Next

  ' Paste array into range.
  Cells(cFirst, cCol).Resize(Cells(Rows.Count, cCol) _
      .End(xlUp).Row - cFirst + 1) = vntFE

End Sub

Sub FillEmptyEasy()

  Const cCol As Variant = "J"      ' Column Letter/Number
  Const cFirst As Long = 2         ' First Row

  Dim rng As Range                 ' Range
  Dim vntFE As Variant             ' Range Array
  Dim LastRow As Long              ' Last Row
  Dim i As Long                    ' Range Array Rows Counter

  ' Calculate Last Row.
  LastRow = Cells(Rows.Count, cCol).End(xlUp).Row
  ' Calculate Range.
  Set rng = Cells(cFirst, cCol).Resize(LastRow - cFirst + 1)
  ' Paste range into array.
  vntFE = rng

  ' Loop through array and perform calculation.
  For i = 1 To UBound(vntFE)
    If vntFE(i, 1) = "" Then vntFE(i, 1) = "=IFERROR((IF(LEFT(RC[-9],6)" _
        & "=""master"", get_areas(RC[-7]), """")),"""")"
  Next

  ' Paste array into range.
  rng = vntFE

End Sub

2.代码

Sub NoComma()

  Const cCol As Variant = "J"      ' Column Letter/Number
  Const cFirst As Long = 2         ' First Row

  Dim vntNoC As Variant            ' Range Array
  Dim i As Long                    ' Range Array Rows Counter

  ' Paste range into array.
  vntNoC = Cells(cFirst, cCol).Resize(Cells(Rows.Count, cCol) _
      .End(xlUp).Row - cFirst + 1)

  ' Loop through array and perform calculation.
  For i = 1 To UBound(vntNoC)
    If Right(vntNoC(i, 1), 1) = "," Then _
        vntNoC(i, 1) = Left(vntNoC(i, 1), Len(vntNoC(i, 1)) - 1)
  Next

  ' Paste array into range.
  Cells(cFirst, cCol).Resize(Cells(Rows.Count, cCol) _
      .End(xlUp).Row - cFirst + 1) = vntNoC

End Sub


Sub NoCommaEasy()

  Const cCol As Variant = "J"      ' Column Letter/Number
  Const cFirst As Long = 2         ' First Row

  Dim rng As Range                 ' Range
  Dim vntNoC As Variant            ' Range Array
  Dim lastrow As Long              ' Last Row
  Dim i As Long                    ' Range Array Rows Counter

  ' Calculate Last Row.
  lastrow = Cells(Rows.Count, cCol).End(xlUp).Row
  ' Calculate Range.
  Set rng = Cells(cFirst, cCol).Resize(lastrow - cFirst + 1)
  ' Paste range into array.
  vntNoC = rng

  ' Loop through array and perform calculation.
  For i = 1 To UBound(vntNoC)
    If Right(vntNoC(i, 1), 1) = "," Then _
        vntNoC(i, 1) = Left(vntNoC(i, 1), Len(vntNoC(i, 1)) - 1)
  Next

  ' Paste array into range.
  rng = vntNoC

End Sub