vba - 宏观公式的变化

Question

我有一个看起来像这样的 Excel 文件:



我想让它看起来像这样:



我正在使用的代码是:

   Sub FindString()
   Dim A As Range, r As Range, last As Range
   Set A = Intersect(ActiveSheet.UsedRange, Range("A:A"))

   For Each r In A
       If IsNumeric(Left(r, 6)) Then Set last = r
       If Not last Is Nothing Then last.Copy r.Offset(0, 1)
  Next r
 End Sub

它可以工作，但如果行数超过 50,000 会挂起 Excel。所以我在这个网站上找到了一些帮助并改为:

Sub Demo()
Dim r As Range

With ActiveSheet
    Set r = .Range(.Cells(4, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

r.Offset(0, 1).Formula = "=IF(ISNUMBER(VALUE(LEFT(A4,6))),A4,B3)"
r.Offset(0, 1) = r.Offset(0, 1).Value

End Sub

现在的麻烦是我不明白这个位是什么:

"=IF(ISNUMBER(VALUE(LEFT(A4,6))),A4,B3)"  

在公式中正在做，如果在我当前的公式中，如果我更改为 if 条件:

  If IsNumeric(Left(r, 6)) And 0 = InStr(r, "Totals:")

如何更改类似于 sub DEMO() ？

Answer 1

如果您想要更快的速度，请使用类似这样的东西而不是您发布的演示代码。这段代码也更灵活。唯一的

Sub Demo2()
    Application.ScreenUpdating = False
    Dim A() As Variant
    A = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value2
    Dim B() As Variant
    ReDim B(1 To UBound(A), 1 To 1)
    Dim ID As String
    For i = 1 To UBound(A)
        If IsNumeric(Left(A(i, 1), 1)) Then
            ID = A(i, 1)
            'If you want to use a dynamic equation, use the below line instead
            'ID = "=A" & i
        End If
        B(i, 1) = ID
    Next
    Range("B1:B" & UBound(B)) = B
End Sub